make hyperlinks follow the copied sheet, not the original

[dadouza]

I am making a workbook where all the sheets will be a copy of the original,
but named differently. I want to make one sheet, with all its hyperlinks to
different parts of the document, and then just make a copy of that sheet and
rename. But what I really want is, the hyperlinks in the copied sheet (after
renaming) to stay with that sheet, and not jump back to the original sheet.
i.e. sheet 1's hyperlinks stay in sheet 1, but sheet 2's hyperlinks jump
within sheet 2, not back to sheet 1 where they were copied from. There must
be a way to do this, and save me a lot of work. The finshed book will contain
about 100 sheets, with about 50 hyperlinks per sheet. To do this manually
will take forever.
Thanks in advance for taking the time to read this, hope you can help me.
Regards
Paul

 

[Gary''s Student]

You can create hyperlinks that wil always track to the worksheet they are on.

Create and save a workbook (the save is important)

In some cell in Sheet1, enter:

=HYPERLINK("#"&CELL("address",Sheet1!Z100),"target is relative")

click on the link and you will go to Z100.

If you rename the worksheet, the hyperlink will adjust.
If you copy the worksheet and rename the copy, the hyperlink will adjust and
not go back to the original sheet.

 

[dadouza]

Thank you very much, you are the top person on my christmas card list. Very
easy solution to do and fits my requirements exactly.

Many thanks & Kind Regards

Paul

 

[Gary''s Student]

Glad I could help, Paul.
Have a pleasant day.