This Excel Factor tip was sent in by Jerry Beaucaire of Bakersfield, California.
Words by Mynda Treacy
Hyperlinks can make navigating your workbooks quick and easy but they (usually) take a bit of work to set up, especially if you use the HYPERLINK Function.
The HYPERLINK Function syntax is:
link_location is the cell you want to jump to.
friendly_name is an optional name you can use as the blue underlined text displayed in the cell.
For example; if I wanted to put a link in cell D1 to take me to cell D10 I could use this formula:
Note how I have to include the file name and the sheet name in the ‘link_location’ argument. It’s a hassle.
But not anymore. The other day I stumbled upon Jerry’s genius use of the # symbol to create a relative reference for the hyperlink.
Now, I know you’re probably thinking why don’t I just use the Insert Hyperlink dialog box and be done with it…well, I could but that is only good if I want one static link.
You see, the advantage for using the HYPERLINK function is that you can build dynamic hyperlinks using functions that reference other cells. Once you build one formula you can copy and paste it to automatically create more, like this:
But first…what’s the magic # all about?
The # symbol works like a relative reference for a hyperlink. So, in the example above instead of having to type out the file name you can just insert #.
Here are some ‘Before’ and ‘After’ examples which illustrate just how handy the # symbol is:
Hyperlink Formula – Go to cell D10 on the Same Sheet
Before magic #:
=HYPERLINK("[ef18.xlsx]Sheet1!D10","Go to D10")
Or with a formula that automatically detects the file and sheet names (this is important if you send the file to other’s who are likely to rename the file and,or change the sheet name):
=HYPERLINK(CONCATENATE("[",MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,LEN(CELL("filename"))-SEARCH("[",CELL("filename"))),"!D10"),"Go to D10")
Ugh. That formula is 150 characters long!
After magic # – the formula is only 30 characters:
=HYPERLINK("#D10","Go to D10")
Hyperlink Formula – Go to cell D10 on a Different Sheet
Before magic #:
=HYPERLINK("[ef18.xlsx]Sheet2!D10","Go to Sheet2 D10")
After magic #:
=HYPERLINK("#Sheet2!D10","Go to Sheet2 D10")
Now you can see how great the magic # is, let’s look at a clever use for the Hyperlink function.
Dynamic Hyperlink Lookup
Let’s say on Sheet1 we have a list of dog breeds and we want to insert a hyperlink to take us to the matching breed on Sheet2.
This is Sheet 2:
On Sheet1 (below) we can use the ADDRESS and MATCH functions to find the address (cell reference) of the matching breed on Sheet2 like this:
The ADDRESS function creates a cell reference as text, given specified row and column numbers. The syntax is:
We only need to complete the row_num, column_num and sheet_text arguments of the ADDRESS function for this formula (the other arguments are optional, hence the square brackets  ).
To find the row_num we use the MATCH function to lookup the value in D2 in the range A1:A10 on Sheet2 and return the position i.e. the cell number in the range A1:A10.
Our formula is:
And it evaluates like this:
Step 1 – Poodle is on the 6th row in column A on Sheet2, and since we’re only referencing one column the col_num must be 1, and the sheet name is Sheet2.
Step 2 – The ADDRESS function returns the cell reference which is required for the HYPERLINK formula and rearranges the position of ‘Sheet2’ in front of the cell reference.
Step 3 – The ampersands concatenate all parts of the formula together to give our HYPERLINK function link_location argument.
This is just one example of how you can use dynamic hyperlinks. Jerry shows a few more examples on his blog. Plus, you can download a workbook for some clever array formulas that also locate the sheet names, which is handy if you’re working with more than one sheet.
Thanks, Jerry for allowing me to share your tip in our Excel Factor series.
Jerry has been using Excel for 20+ years, training and teaching on various Excel Forums for the past 6 years. He is currently one of the Excel Forum Gurus and a Moderator at ExcelForum.com, as well as a registered Software Expert at AskMeHelpDesk.com. He was awarded MVP in Excel by Microsoft in 2010.
Jerry Beaucaire is currently the Director of Training, DevStudios America, LLC. He runs a private Excel programming and Consulting business and finds Excel to be “as fun and addicting as Soduko.”
In his spare time he is Minister of Music, home chef and a Model Rocketry officianado, he administers the web site: www.MadRocketScientist.com
Vote for Jerry
If you’d like to vote for Jerry’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Jerry for taking the time to suggest this tip….or all of the above 🙂