MS Excel — Surprise Your Colleagues with this Advanced HYPERLINK Technique!
Take Advantage of this in your workbooks!
I use hyperlinks frequently for easy navigation in my #Excel workbooks.
What I could never figure out was whether I could hyperlink to a Named Range? Turns out it’s fairly easy to do. 😀
The HYPERLINK function syntax is
=HYPERLINK (link_location, [friendly_name])
Easy enough — but to use a Named Range, you just have to put a “#” sign in front of it.
=HYPERLINK(“#”&F1,”Click Here to GoTo”), where F1 is the cell containing the named range (see image above).
Learn more from ExcelJet.com.
Excel HYPERLINK Function
The Excel HYPERLINK function returns a hyperlink from a given destination and link text. You can use HYPERLINK to…
But, Did You Know?
The interesting part is the HYPERLINK function will also accept functions as an argument, so in the example below, I reference the named range “defined range” by using a VLOOKUP (to a Table called “tbl_RangeNamesList”).
=HYPERLINK(“#”&VLOOKUP($F$1,tbl_RangeNamesList[[Range Names]:[Reference]],2),”Click Here to GoTo”)
Not sure I would ever use that, but I thought it was cool you could actually do that in a hyperlink reference. 😁
I will be using this frequently going forward in my models!
Just in case you didn’t know this — now you do!