Hyperlink formula

[ninobomba]

Hi

Your help is much appreciated. I am trying to build a table of contents and would like to hyperlink/ Reference text from another spreadsheet.

I cant just simply hyperlink the required cell in Sheet2 since it would eventually break the linkage if i add a new row or more data to the sheet. I heard using =Hyperlink with Vlookup or Index would work but i cant seem to successfully make the formula work.

Below is just a simple example:

So in Sheet2 I have the below

Sheet1

I would like to Hyperlink "Cash in Transit" so when i click on it in Sheet 1 it takes me straight to sheet 2 "Cash in Transit"

same thing with Accounts payable.

Thank you,

Felipe

 

[Special-K99]

I did something like this by creating a Named Range, e.g. CashInTransit then hyperlinking to that.
This way when I entered additional rows or deleted them the link always went to CashInTransit regardless.

 

[RoryA]

You could also use something like:

=HYPERLINK("#Sheet2!A"&MATCH("Cash in Transit",Sheet2!A:A,0))

but a named range is a safer approach.

 

[ninobomba]

I did something like this by creating a Named Range, e.g. CashInTransit then hyperlinking to that.
This way when I entered additional rows or deleted them the link always went to CashInTransit regardless.

Oh sweet that works! thanks so much