Search code examples
excelexcel-formulahyperlink

Excel: preserve hyperlink to the cell in the current sheet after copying the sheet

I have an Excel file (containing Sheet1 only), with hyperlink formula in cell B3 (=HYPERLINK("#'Sheet1'!A1","link")) pointing to cell A1 of Sheet1. Then I copy Sheet1 (so have now Sheet1 and Sheet1(2)). Desired result: hyperlink formula in B2 of (a new) Sheet2 pointing to cell A1 of a new Sheet1(2) after clicking (by default it is pointing to cell A1 of old Sheet1). How can I improve the formula (=HYPERLINK("#'Sheet1'!A1","link")) so that it is pointing to the cell A1 of the new (current) spreadsheet Sheet1(2) (=I would like to have a (relative) reference to the current sheet robust to copying of sheet instead of (absolute) reference to the initial sheet)? Many thanks in advance!

Solution

  • You need to exclude sheet's name from the hyperlink:

    =HYPERLINK("#A1","link")