Ask Your Question

testy's profile - activity

2019-06-12 17:31:57 +0100 marked best answer Calc - hyperlinks that dynamically adjust to sheet re-organisation

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.

2019-06-12 17:31:57 +0100 received badge  Scholar (source)
2019-06-12 17:31:51 +0100 received badge  Notable Question (source)
2019-06-05 21:13:14 +0100 commented answer Calc - hyperlinks that dynamically adjust to sheet re-organisation

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

2019-06-05 21:09:42 +0100 commented question Calc - hyperlinks that dynamically adjust to sheet re-organisation

Ah, I see. Done.

2019-06-05 21:09:26 +0100 edited question Calc - hyperlinks that dynamically adjust to sheet re-organisation

Calc - hyperlinks that dynamically adjust to sheet re-organisation I have used hyperlinks to assist navigation of a spra

2019-06-05 21:08:47 +0100 received badge  Popular Question (source)
2019-06-04 20:22:26 +0100 asked a question Calc - hyperlinks that dynamically adjust to sheet re-organisation

Calc - hyperlinks that dynamically adjust to sheet re-organisation I have used hyperlinks to assist navigation of a spra