How to prevent row/column insertion/deletion from breaking links to other sheets?

Question

Earlier I asked this question: How to create a link with content bound to other cell's content in Google sheets?

And until now the provided solution was working perfectly for me but now, the problem is: I just realized that whenever I insert/delete a row/column from my referenced sheet the links generated using the aforementioned question's answer will become broken and will link to an other cell/range.

Here's an example to reproduce:

• On a first sheet, fill A1 with some text
• On a second sheet, create a link to A1 (using the aforementioned question's answer)
• Use the link to come back to the first sheet, you should have your cell with text selected
• Insert a row above row 1
• Come back to second sheet and try to use the link again : it now selected an empty cell on row one instead of selecting the cell with your text

How can I overcome this problem and have "stable" links that always send me to the same cell using its "reference" and not its location?

Answer 1

I found the trick to fix this issue : using the link as explained in the answer to the question mentionned in this question, in the gid=someId&range=SomeLocation part, remove the right part of the = (to end up with something like gid=someId&range=). Use the CONCATENATE function to build the HYPERLINK's first parameter and build the right part of the = using ROW and/or COLUMN functions.

For example, using the question's example, the cell containing the hyperlink's formula will look like this : =HYPERLINK(CONCATENATE("gid=someId&range=";COLUMN(FirstSheet!A1);ROW(FirstSheet!A1));"This is a stable link")

Answer 2

Instead of using actual references, i.e. A1, use INDIRECT, i.e. INDIRECT("A1")

Related

• Lock the formula of a cell, even if the cells it references change
• Prevent dragging and dropping from changing cells referencing moved cells
• Google Sheets Cell Reference
• Locking a formula when adding rows