ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Hyperlinks with lookups (https://www.excelbanter.com/excel-worksheet-functions/448510-hyperlinks-lookups.html)

[email protected]

Hyperlinks with lookups
 
I want to hyperlink from cells in one range to cells in another range. What I have below works, but there has to be a more elegant formula.

So this is Book1.xls (has to be saved as such).
Column A Rows 1-4 are strings "(numerical)", "one", "two", "three"
Column B Row 1 is string "HYPERLINK"
Column C Rows 1-4 are strings "(alphabetical)", "one", "three", "two"
Named Range "Alpha" is C2:C4
Column B Rows 2-4 have the hyperlinks: B2 is:
=HYPERLINK(ADDRESS(ROW(Alpha)+MATCH(A2,Alpha,0)-1,COLUMN(Alpha),1,TRUE,"[Book1.xls]Sheet1"),A2)
and copy to B3 and B4

Clicking on B2,B3,B4 correctly links to C2,C4,C3. But the formulas are quite long strings. Can they be reduced? TIA, Dave


All times are GMT +1. The time now is 10:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com