Hyperlink to cell in same document without full path?

Bort · August 18, 2021, 10:16am

I’d like to create hyperlinks to specific cells in a sheet in the same document from another sheet in that same document. Is there a way to write the link without Calc ridiculously insisting on using the entire system path every single time so the file can be opened and all the links work regardless of where it’s located on a hard drive? It’s in the same document.

For example I want to create a hyperlink in sheet2 that links cell A20 in sheet1.

=hyperlink($Sheet1.A20, “click”)

Throws an error.

How do I do that without Calc insisting the link is the full file path on my system??
Not everyone is going to save this file after they download it to the same exact file location. There’s a very slim chance they’re going to have the same exact windows username I have.

LeroyG · August 18, 2021, 11:03am

I don’t know the exact use that you will give to it, but I think that you can begin choosing menu Insert - Hyperlink - Document, then for Target use $Sheet1.A20, and for Text use click.

karolus · August 18, 2021, 11:23am

$Sheet1.A20 #reference to *content* of Sheet1.A20
# but HYPERLINK needs the String itself with prefix '#':
"#$Sheet1.A20"

Lupp · August 18, 2021, 11:49am

“Throws an error” isn’t of much value. Next time please give info about what you tried when the error was thrown, and in what way the error showed (in this case the error message).

In fact the error isn’t triggered by the formula (like #NA! e.g.) but when attempting to use the link.

Lupp · August 18, 2021, 11:00am

$Sheet1.A20 isn’t a target, but refers to the content of a cell.
use =HYPERLINK("#"&CELL("address";$Sheet1.A20);"click")
or =HYPERLINK("#$Sheet1.A20";"click") if there’s no need of any adaption of relative parts of the address concerning Fill or Copy/Paste.

For demo see attachment:
disask67086InternalHYPERLINKtargetDemo.ods (11.5 KB)

Bort · August 18, 2021, 7:14pm

I’m not familiar with Libre Office or any Office suite lingo or terms.
I don’t have any idea what any of that means.

How would I do this for a button that links a cell in another sheet?
What would I enter in the URL field of the button control properties?

karolus · August 18, 2021, 7:26pm

#$Sheet1.A20

Bort · August 18, 2021, 8:25pm

Thanks a lot everyone!

foudoc · March 2, 2023, 11:46pm

Thanks a lot for highlighting the combination of HYPERLINK() and CELL(). I used it for creating my TOC (“table of contents”) and “quick links” within my worksheet. Just in case it’s helping some, that is what I did:

I have a sheet called TOC listing the number of my “chapters” in row A, the “heading” (link text) in row B and the “cell address” (link target) in row C.
The “cell address” is generated using =CELL("address";$Sheet1.A1).
The links in my “table of contents” or “quick links” on any sheet are created by =HYPERLINK("#"&$TOC.C1;$TOC.A1&" "&$TOC.B1) , =HYPERLINK("#"&$TOC.C2;$TOC.A2&" "&$TOC.B2) , …
The “link text” is containing the number of the “chapter” $TOC.A1, followed by two blanks " " and the “heading” of the chapter $TOC.B1.
When renaming a sheet the cell containing the “cell address” needs to be refreshed using F9. That is refreshing all the created links using HYPERLINK() that are pointing to that cell which is in my case much faster than manually updating all links created with Ctrl + K in the first place.
My “headings” are referencing to the corresponding cell $TOC.B1, $TOC.B2, … This way I can update everything in one place: Changing the “heading” in row B is both updating all my links to that chapter and the heading itself.

It seems to work for now.

So thanks once again!