0

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?

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

2 Answers 2

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

0

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

Related

3
  • 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, 2020 at 21:33
  • I didn't suggested to use INDIRECT instead of HYPERLINK
    – Rubén
    Nov 28, 2020 at 21:34
  • 1
    Then how am I meant to use INDIRECT to have a link like HYPERLINK?
    – nalka
    Nov 28, 2020 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.