Dynamic Hyperlinks in Excel
Dynamic Hyperlinks in Excel to navigate between Worksheets
Adding a hyperlink to a specific worksheet or cell is an easy thing to do. But what if you need the hyperlink to be dynamic, no matter if you insert or delete sheets, change the order of sheets?
Or, if you start from a template sheet, and keep adding sheets, you need to have dynamic hyperlinks in the template sheet, that “knows” which is the next or previous sheet, without manually adjusting all hyperlinks.
Few things you should know:
You have multiple options to create a hyperlink (without VBA):
- you can add a hyperlink to a cell, by right clicking that cell and selecting Hyperlink from right click menu;
- add a hyperlink to a shape, by right clicking that cell and selecting Hyperlink from right click menu; (very similar to adding a hyperlink to a cell)
- add a hyperlink in a cell using the Excel Hyperlink Function.
There is a major difference between first two options and option 3:
Adding a hyperlink to a cell or to a shape is using a range created from an address:
=INDIRECT(ADDRESS(1,1,,,Active_Sheet))
The 3rd option, that uses the Excel Hyperlink formula, expects an address as a text string, not a range created by the INDIRECT function, because the formula works by default with URL addresses too, not only with cell ranges:
=ADDRESS(1,1,,,Active_Sheet)
You can download the sample files from here:
Hyperlinks to navigate between sheets.xlsx
Hyperlinks to navigate between sheets using GET.WORKBOOK.xlsm
How to create Dynamic Hyperlinks in Excel
The method used in the sample files provided is based on Defined Names.
There is a major difference between these 2 files:
- Hyperlinks to navigate between sheets.xlsx has a list of sheets manually created, where you can decide the navigation order
- Hyperlinks to navigate between sheets using GET.WORKBOOK.xlsm is using an old Excel 4 macro function – GET.WORKBOOK – to extract the complete list of sheets from current workbook.
The version using GET.WORKBOOK function, will not allow you to set the order of sheets, but it’s completely dynamic, you don’t have to adjust the sheets list if you added new sheets or if you deleted some of the existing sheets.
Steps required to create the Dynamic Hyperlinks:
STEP 1: Create 6 defined names, using the command Define Name from the Formulas Tab, Defined Names section, or Right-Click any sheet cell, choose Define Name from right click menu; the names are: Active_Sheet, TopPage, EndPage, NextPage, PreviousPage, SheetList;
The Active_Sheet name will have the following formula in the RefersTo field:
=MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,255)
The SheetList name will have the following formula in the RefersTo field:
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255)
Please note that GET.WORKBOOK formula can be used only in a defined name, not in worksheet formulas.
The rest of the names: TopPage, EndPage, NextPage, PreviousPage, must have a simple cell reference in RefersTo field, like =Sheet1!A1 , we will change this later!
STEP 2: Insert 4 hyperlinks in cells or in shapes, referring to those 4 navigation names created in STEP 1: TopPage, EndPage, NextPage, PreviousPage
To insert hyperlinks, right click the cell or the shape, and choose Hyperlink from the right click menu, or, using the ribbon, from the Insert Tab, Links section, choose Hyperlinks command; The Insert Hyperlink Menu will be displayed:
Choose Place in this Document button from the left side, then choose the corresponding name from the list of Defined Names.
Important: When setting hyperlinks for shapes or cells, in the list of defined names from the Hyperlinks menu names that contains formulas are NOT shown! (it’s a default security setting for Excel),
Only creating the names with a simple reference like we did in STEP 1 will allow our name to be displayed in the list of defined names. This way you can bypass that security limitation for hyperlinks.
STEP 3: Now, we can edit the formulas for the navigation names: TopPage, EndPage, NextPage, PreviousPage.
Go to Formulas Tab in ribbon, Defined Names section, and press on Name Manager; this will open the Name Manager window;
– select the TopPage name, and insert the following formula in RefersTo field:
=INDIRECT(ADDRESS(1,1,,,Active_Sheet))
– select the EndPage name, and insert the following formula in RefersTo field:
=INDIRECT(ADDRESS(22,1,,,Active_Sheet)
TIP: instead of row 22 used in this example, you can set a formula to determine the last used row in active sheet
-select the NextPage name, and insert the following formula in RefersTo field:
=INDIRECT(ADDRESS(1,1,,,IFERROR(INDEX(SheetList,MATCH(Active_Sheet,SheetList,0)+1),"First Page")))
Note that when we reach the last sheet in list, the INDEX function will result in an error, and IFERROR will return the First Page to start from the beginning…
– select the PreviousPage name, and insert the following formula in RefersTo field:
=INDIRECT(ADDRESS(1,1,,,INDEX(SheetList,MATCH(Active_Sheet,SheetList,0)-1)))
That’s all! Now, the formulas we set on names will detect the next or previous sheet, no matter if you add new sheets between existing sheets, or if you delete sheets, or even if you change sheets order!
For the excel HYPERLINK function examples, using the pound sign “#” (also called as hashtag), will make the function dynamic, it will replace the workbook name:
=HYPERLINK(“#”&ADDRESS(1,1,,,Active_Sheet))
If you don’t use the “#” hashtag, then you have to add the workbook name before sheet reference:
=HYPERLINK(“[Hyperlinks to navigate between sheets using GET.WORKBOOK.xlsm]”&ADDRESS(1,1,,,Active_Sheet))
Feel free to leave a comment for this article, if you have other tips related to Excel Hyperlinks.