• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reference is not valid

Thomas Kuriakose

Active Member
Respected Sirs,

Compliments of the season.

We have a workbook, which has cities and points as in the attached file. The cities are in Index tab J3:J7. The points (G3:G20) are distributed in multiple sheet tabs.
Index tab:
a) Cell B3 has cities with data validation from list in J3:J7
b) Cells B4 down has the points matched based on the selected city in B3.

We need to have a hyperlink from the values in cells B4 down linked to the respective points sheets. I tried to use the hyperlink formula, but it does not work. I am not able to use the cell reference derived in cells B4 down to reference the sheet tab in hyperlink.

Kindly help with the below -

a) How to get the hyperlink without a helper column in cells B4 down.
b) How to use cell reference in friendly name syntax if possible as in formula C4 and D4

Thank you very much for kindly support and guidance always,

with regards,
thomas
 

Attachments

bosco_yip

Excel Ninja
Maybe...........

B4, copied down :

=IFERROR(HYPERLINK("#G"&AGGREGATE(15,6,ROW($F$3:$F$20)/($B$3=$F$3:$F$20),ROW(A1)),INDEX($G$1:$G$20,AGGREGATE(15,6,ROW($F$3:$F$20)/($B$3=$F$3:$F$20),ROW(A1)))),"")

C4, copied down :

=IF(B4="","",HYPERLINK("#"&B4&"!A1",B4))

Regards
Bosco
 

Thomas Kuriakose

Active Member
Respected Sir,

Thank you very much for this great solution provided. This works perfectly

Sir, one tweak required for the formula in B4 down, how can we use the hyperlink to move to respective sheet tab and not the cells in G3:G20.

Thank you so much for your great support,

Much appreciated,

with regards,
thomas
 

bosco_yip

Excel Ninja
................Sir, one tweak required for the formula in B4 down, how can we use the hyperlink to move to respective sheet tab and not the cells in G3:G20...................
thomas
Then,

B4 formula changed to as per following and copied down :

=IFERROR(HYPERLINK("#"&INDIRECT("G"&AGGREGATE(15,6,ROW($F$3:$F$20)/($B$3=$F$3:$F$20),ROW(A1)))&"!A1",INDEX($G$1:$G$20,AGGREGATE(15,6,ROW($F$3:$F$20)/($B$3=$F$3:$F$20),ROW(A1)))),"")

Regards
Bosco
 
Top