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. 😀
Formula Syntax
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.
For example:
=HYPERLINK(“#”&F1,”Click Here to GoTo”), where F1 is the cell containing the named range (see image above).
Learn more from ExcelJet.com.
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!
This post was originally shared on LinkedIn. Check out #twinztalk and #twinztechtip.