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.