CODEWITHSUNDEEP
excelexcel-formulahyperlink
Champion
Champion

Reputation: 105

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!

Upvotes: 0

Views: 59

Answers (2)

nkalvi
nkalvi

Reputation: 2614

rotabor's shorter method works well.

As rotabor commented, you could

  • Define a name in Name Manager for the cell, for example target1=Sheet1!$A$1, with scope set to Sheet1
  • Use this in hyperlink as =HYPERLINK("#target1","link")
  • When copying the sheet, the name also will be created scoped to the new sheet, so the hyperlink will work locally

Name Manager

You could try

=LET(
    n, CELL("filename"),
    sh, REPLACE(n, 1, FIND("]", n), ""),
    HYPERLINK("#'" & sh & "'!A1", "link")
)

Upvotes: 0

rotabor
rotabor

Reputation: 4698

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

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

Upvotes: 1

Related Questions