D
davedbarc
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
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