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?

  • As questions should be self-contained, show in this question what you tried. – Rubén Nov 28 '20 at 21:36

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")


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


  • 1
    using INDIRECT doesn't give me a link like HYPERLINK does and moreover it is still affected by row/column deletion/inserstion the same way it is with my actual linking method – nalka Nov 28 '20 at 21:33
  • I didn't suggested to use INDIRECT instead of HYPERLINK – Rubén Nov 28 '20 at 21:34
  • 1
    Then how am I meant to use INDIRECT to have a link like HYPERLINK? – nalka Nov 28 '20 at 21:35

Your Answer

