Ask Your Question
0

Calc - hyperlinks that dynamically adjust to sheet re-organisation

asked 2019-06-04 20:22:26 +0100

testy gravatar image

updated 2019-06-05 21:09:26 +0100

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.

edit retag flag offensive close merge delete

Comments

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: https://ask.libreoffice.org/en/questi....

Lupp gravatar imageLupp ( 2019-06-04 23:37:16 +0100 )edit

Ah, I see. Done.

testy gravatar imagetesty ( 2019-06-05 21:09:42 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-06-04 23:35:06 +0100

Lupp gravatar image

updated 2019-06-04 23:38:41 +0100

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.

edit flag offensive delete link more

Comments

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

testy gravatar imagetesty ( 2019-06-05 21:13:14 +0100 )edit

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

Lupp gravatar imageLupp ( 2019-06-05 22:42:04 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-04 20:22:26 +0100

Seen: 39 times

Last updated: Jun 05