How can I make a Hyperlink to a cell within another worksheet rela

[Guest]

I have lists in a workbook with hyperlinks to a specific cell within a
specific worksheet.

Ex: Master Sheet | Cell A3 - links to - Sheet 2| Cell A1
Sheet 2 | Cell A1 - links to Master Sheet | Cell A6

If I change any order of cells, I lose the hyperlink. How can I make it so
that if I "sorted" the rows in the Master Sheet the Sheet 2 hyperlinks will
find what was formerly Cell A6?

Hope this makes sense.

 

[Dave Peterson]

Use another cell in an adjacent column and use the =hyperlink() function.

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

 

[Guest]

Sorry Dave (and David)

You are dealing with a novice here. I have no idea where to put:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

....or what it means.

Can you "spell it out for me"?

Thanks.

 

[Dave Peterson]

Say you want to link to C5 on a worksheet named "Sheet two", put this in a cell:

=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
or
=HYPERLINK("#"&CELL("address",'sheet two'!C5),"Click me")

 

[Guest]

Thanks, Dave.

I understand how to do what you wrote here.

One last question: So, I have to do this for EACH cell? I can't do
something that would work for an entire column?

 

[Dave Peterson]

You could put the location data in another cell and then use the formula in an
adjacent cell.

If you decide to try that, you could put the address of the other cell and use
something like:

I put this in A1:
''sheet2'!c5

That's two apostrophes--the first one tells excel to treat the cell like text
and the second one is used when the sheet name has spaces--you only see one
leading apostrophe in the cell (you'll see two leading apostrophes in the
formula bar).

And a formula like:
=HYPERLINK("#"&CELL("address",INDIRECT(A1)),"Click me")
to create the link

Thanks, Dave.

I understand how to do what you wrote here.

One last question: So, I have to do this for EACH cell? I can't do
something that would work for an entire column?