If you have any spreadsheets where one sheet has some kind of list and the other sheet has some details on the items in the list, you will understand how confusing it can be navigating from one to the other. Microsoft Excel has the ability to create hyperlinks between items in two sheets?
In this article we develop a macro that creates a link between an item in a list and a corresponding worksheet.
To create the example we used two templates found in Microsoft Office Online
- Employee Profile
- Employee attendance tracker
We programmatically create the hyperlink from one cell to another cell so that the user can go from the attendance to the profile and back again.
How to Use the Macro
Download our example Excel file from here so you can follow along with us.
- In the employee profile sheet select the employee name cell.
- Press Ctrl-Shift-L
- A link to the the employee’s attendance sheet will be created.
- In the employee’s attendance sheet a link to the employee profile sheet will be created
How it works
The main trick of this macro is in the formula to create a hyperlink:
=HYPERLINK("[File Name]'Sheet Name'! Cell Name","Friendly Name")
Here we used a handy function called ColumnLetter in order to convert the Column number into a Letter. That code was taken from here: Convert an Excel column number to its string equivalent.
Summary
By hyperlinking between related information in your spreadsheets you could improve productivity and reduce errors by quite an amount. Anything that removes the hunting and searching process will allow your users to get right to the information they need. How else might you use this macro?
About the author
Yoav Ezer is the CEO of a company that produces PDF to XLS conversion software and also co-author of the technology and productivity blog Codswallop.
For more Excel tips from Yoav, join him on Facebook or Twitter.
Popularity: 3% [?]
Post a Comment