08
Apr 2013
- BY Kevin Lehrbass
- POSTED IN Solutions
- WITH 1 COMMENTS
- PERMALINK
- STANDARD POST TYPE
Create a dynamic hyperlink using an array formula!
This means that you can jump to a location in your data depending on conditions in the values.
This is one of my favorite ways to use an array formula!
In fact, Debra Dalgleish thought my hyperlink array was cool. She wrote an blog post about it! Thanks Deb!
http://blog.contextures.com/archives/2013/04/04/create-a-dynamic-hyperlink/
Download my Excel file
Download here or via my OneDrive (file 00035)
The Hyperlink Array Formula
=HYPERLINK(“#”&”E”&MIN(IF((H7=E20:E119)*(MAX(IF((H7=E20:E119),G20:G119,””))=G20:G119),ROW(E20:E119),””)),”Link: largest city “&H7)
(it’s an array formula so you must hold and press ‘Ctrl’ key and then ‘Shift’ key and then press ‘Enter’ key… instead of just Enter)
Watch my YouTube video
1 Comments