I recently came upon a challenge that at face value looked straight forward but actually proved to be quite tricky.
What I wanted to do was create a drop-down menu in Excel. Depending on the choice selected, a hyperlink would be created that would allow you to “jump” to a specific location in the Excel workbook based upon your selection.
The solution I hit upon is to use the Hyperlink formula with a unique twist.
First, I created two different worksheets – for this example I’ve creatively named them “Test Sheet 1” & “Test Sheet 2”.
Next, I created a drop-down list menu using the two worksheet names (cell C2 in picture below; see my previous post for details: Excel: Creating a Drop Down Menu).
Finally, in cell C4 I put the following formula
=HYPERLINK("#'"&C2&"'!A1","Go to "&C2) where the first part
"#'"&C2"'!A1" builds the actual link #’Test Sheet 1′!A1 or #’Test Sheet 2′!A1 (depending on the drop down selection) that takes you to the A1 cell on either the Test Sheet 1 or Test Sheet 2 worksheet, and the second half
"Go to "&C2 builds the text that’s displayed to the user in cell C4.
Viola! Depending on the drop down item selected, the hyperlink will dynamically update and direct the user to the correct worksheet.
The tricky part was in figuring out where to put all the single & double quote marks as well as learning about using the hash mark to indicate that the URL was inside the current workbook without having to specify the full name of the workbook.
This should come in handy when building table of contents, forms, &/or surveys in Excel.