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
Pingback: Create a HYPERLINK in Microsoft Excel | My Spreadsheet Lab