Do hyperlinks update when inserting empty rows at the target?

English
#1

I have some cells that link to a cell in another sheet in my document. Think =HYPERLINK("#Sheet2.B4", "link"). In Sheet2, if I add a new row above that cell, B4 is now B5, but the hyperlink doesn’t work. This also doesn’t work with the Ctrl+K, “Target in document” hyperlink style. This makes the links much less useful to me, as I can’t reorder the rows in my sheets (and maybe sorting the rows would also break it? this seems to indicate it might).

Is this intended? Is there some other way to create a clickable link to somewhere else in the same document that stays up-to-date with sorts/edits at the destination?

I was not able to find any question asking the same thing via search engine or on this page. There are two options in LibreOffice Calc → General → Input Settings that I tried as well, “Expand references when new columns/rows are inserted” and “Update references when sorting range of cells”, but I assume references differ from hyperlinks.

#2

"#Sheet2.B4" is a text, not a referenced address, so it is not modified whatever you do on Sheet2.
Something like
=HYPERLINK("#"&MID(CELL(“address”;$Sheet2.A4);2;99);“Link”)
updates the address with row insertions.

1 Like
#3

I get that the hyperlink is a string, but the program is clearly capable of parsing those strings so I thought it might rewrite it anyhow. That’s kind of a monster command for me. I see what it’s doing, it’s just I need to make like… hundreds of these links. Is there really no other way to create a clickable link with a referenced address within the same document?

#4

The rule for the program is: Don’t touch stuff inside quotes, because I the programmer/user say this is my realm. Same for indirect etc. If you are allowd to parse is I will tell you by calling a function etc.
.
So Calc will not parse your address before you click on the hyperlink. Therefore it does not know it reference another sheet. So it can not update the reference when the sheet is changed.
.
Hyperlinks can point to the Internet, where we have no possibility to be notified of changes anyway.

#5

Sure, I half-wonder why they even let you hyperlink within the same document though, then, if it’s going to break the second you edit that document (but only some edits will break it). This seems like entirely the wrong mechanism, and no one has suggested an alternative. Is there no actual infra-document linking? I shouldn’t need to build a string out of a reference to do this, I feel like. This could also just be the level of jank that’s normal in complicated spreadsheets and I’m unaware. I don’t feel like I have a complicated spreadsheet though, it’s basically a SQL database with three tables and links between them. No math, just info that I may want to sort and reference.

#7

It’s your perception that it breaks. You have an expectation that is wrong, that you linked to a specific data; but in fact, the hyperlink points to the fixed address, which never changes - it will always be "#Sheet2.B4". Of course, you can remove Sheet2 and break it. Or change reference syntax. But the same way, you can create a hyperlink to "file:///some/path/to/external_spreadsheet.ods#Sheet2.B4", and then rename/remove that external_spreadsheet.ods.

If I intended to point to the top left cell, and created a hyperlink to "#Sheet2.A1", and then the hyperlink changed to "#Sheet2.B1" because of insertion of a column, then I would consider it broken. Different expectations.

#8

So I feel you have no spreadsheet at all. You have a database report and the features you use could be done in html.
.
As you loaded the data in Calc you have now the problems of the choosen environment.
.
As you wish to have the reference updated/moved you have two ways in Calc: Either keep the Cell outside the quotes, as I explained after the suggestion by @mariosv or you follow the other solution and give a name to your cell, wich you can reference.
.
An alternative would be to re-design your sheet, so the points of reference are never moved…

…it may be useful for others, even if it is no solution for you. So, why prevent it.


And, as explained: Calc doesn’t judge the target inside quotes, when you enter it. So a typo in the named reference @erAck suggested will give an error/malfunction, when you try the link, not when you write the expression.

#6

Name the target cell. E.g. on cell Sheet2.B4 hit Shift+Ctrl+T to focus the Name Box and enter NameToJumpTo and hit Enter (or use the Manage Names Ctrl+F3 dialog to add the name). Then somewhere hit Ctrl+K and as URL enter #NameToJumpTo, or use
=HYPERLINK("#NameToJumpTo";"link text")

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.