Posted by: Yosef B. | April 12, 2016

Building Dynamically Driven Excel Hyperlinks


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).

Excel: Drop Down Menu Example

Excel: Drop Down Menu Example

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.

Excel: Dynamic Hyperlink Formula

Excel: Dynamic Hyperlink Formula

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.

~Yosef

Advertisements

Responses

  1. […] via Building Dynamically Driven Excel Hyperlinks — Excellent Tips […]

  2. Thanks, this answered my problem to hyeperlink excel sheet. I think before that it can be done by macro. But your explanation change my view.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: