Calc - hyperlinks that dynamically adjust to sheet re-organisation

[testy]

I have used hyperlinks to assist navigation of a sprawling sheet. Each hyperlink is intended to take the user to a different cell in the same sheet.

Rather than using the menu, I used the hyperlink function (with the =HYPERLINK(linkaddress, celltext) syntax) to create them in each of the target cells, for example:
=HYPERLINK("#A1";A1)
This is sufficient for generating a link to the target cell which can be followed by holding Ctrl and clicking. So far, so good.

Now I select cell A1 and insert a cell above and insert a column to the left. This, of course, shifts the contents of A1 to B2. Returning to my cell containing the hyperlink formula, it will now display the following:
=HYPERLINK("#A1";B2)
This is to be expected, due to the syntax used in the linkaddress portion of the hyperlink function.

My question is, therefore, is it possible to use the hyperlink function to create clickable links to other cells in the same sheet where the hyperlink is anchored to the value of a given cell, even where the cell address is changed?

As expected, removing the quotes (=HYPERLINK(A1;A1)) achieves this aim but the resulting link no longer functions as desired and merely produces this error: ‘“celltext” is not an absolute URL that can be passed to an external application to open it.’ Is there another way to do this? Is this a bug?

I am using Calc from LO 6.0.7.3 on Ubuntu 18.04.

[Lupp]

Please use the semicolon as the parameter delimiter in international forums. It is the only character in this role that is globally accepted.
See also: Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?.

[testy]

Ah, I see. Done.

[Lupp]

Use the ADDRESS() function or the CELL() function with the first parameter “ADDRESS”.
HYPERLINK("#" & CELL(“address”; A1); B1) contains relative addressing for everything you need, e.g.

[testy]

Thank you for the prompt response. This works perfectly. I don’t know much about spreadsheets so this is immensely helpful.

[Lupp]

You might consider to click the (still white in grey) checkmark next to my answer.

[foudoc]

I didn’t find this solution when looking for a way to create a “table of contents” (TOC) using the HYPERLINK() function but found a similar one: Hyperlink to cell in same document … [Solution].

The description of this question fits this purpose even better. So for others to find this solution more easily I refer to it here: Create a TOC in CALC using HYPERLINK() and CELL().