Hyperlinks are Excel’s shortcut way to jump to the required data point in the same workbook, different workbook, or an external URL. While hyperlinks are mostly static links, here is the way you can use to create dynamic hyperlinks.
By Dynamic Hyperlinks I mean links that changes based on the selection (or any other user action)
Suppose I have the data set as shown below:
This is the back end data, and I have a summary sheet where I have drop downs where the person can select the Month. The idea is to update the hyperlink with the selections so that it takes the user to the right cell when the hyperlink is clicked. Something as shown below
Here to Create Dynamic Hyperlinks in Excel
- Create a data validation drop down in Cell B3, with source as the name of all months in Data tab [Learn how to create a drop down list in Excel]
- Use the following formula in cell C3
=HYPERLINK("#"&"Data!B"&(MATCH(B3,Data!$B$3:$B$26,0)+2),"Click Here to See Data")
- That’s it!! Your Dynamic Hyperlink is Ready
How this works
- # tells the formula to refer in the same workbook
- Data!B is the reference of sheet name and column name
- MATCH(B3,Data!$B$3:$B$26,0) gives the position of the matching month in the list. 2 is added to it as there are 2 data begins from the third row. For example, in case of January 2012, Match formula returns 1, and adding two returns 3. Hence it refers to B3
This is an amazing trick that can come very handy in creating Excel dashboards.
You May Also Like the Following Excel Tutorials: