2

If I create a hyperlink in Excel, and then I make a copy of that sheet, if I click on the hyperlink in the new copy, it would link me back to the cell in the previous sheet and not the newly created sheet.

How can I create a hyperlink that always links to the corresponding cell in the current active sheet?

fixer1234
  • 27,486
Ben
  • 445

1 Answers1

2

Inserted Hyperlinks are absolute references.

You need to edit the hyperlink after creating/copying the sheet and point it to the desired sheet.

The Hyperlink() function is more dynamic and can point to cells that have the link address. You could build the link address as a string based on the current sheet name. Put a formula like this in B1.

=MID(CELL("filename",a1),FIND("[",CELL("filename",a1)),99)&"!"&CELL("address",A1)

Create a link with the Hyperlink function in a cell, like

=HYPERLINK(B1,"go to top")

When you copy the sheet, the formula will reference the current sheet and the link will stay in the current sheet. Tip: use sheet names without spaces.

If the sheet names need to have spaces, the formula will be a bit more complex

=SUBSTITUTE(MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),99),"]","]'")&"'!"&CELL("address",A1)

Note that this formula will only work in a workbook that has already been saved, not a new Book1 that has never been saved.

teylyn
  • 22,743
  • I copied and pasted your formula into B1, all I got was #VALUE! error. – Ben Jun 03 '15 at 10:03
  • Please read all of my post, especially the last sentence. Details matter. You must save the workbook before you can use the formula. If you don't read the details and if you don't follow the instructions, then you will get errors. That is not the fault of the instructor. – teylyn Jun 03 '15 at 10:53