Inserting (linking) a hyperlink to a cell in Calc, independently from the cell value entered

[Alexutzu]

Hello, I recently start using LibreOffice (ver 7.2.2.2, Windows OS) and in one of my column inside the excel files must have a hyperlink independently of the values that particular cell have. Ex: cell value is 23 (could be text or number format) that can change often but the hyperlink must remain the same at all times. Using the “insert hyperlink” option (Ctrl+K) comes with a big minus: when I’m updating the cell value, hyperlink is deleted.

Is this a bug, or I’m doing something wrong?

Thanks for your support!

[Alexutzu]

@erAck what I want to do is to be able to change the values inside the cells, but to keep the hyperlink; see below ss: where are numbers, there is also a hyperlink; while changing the number itself the hyperlink is not altered/deleted. I hope this time I make my self clear.

[EarnestAl]

Zombie hyperlink
You could have an underlying rectangle shape with hyperlink as background to the cell. Important: this is always in the same place so if you insert a row or column before it won’t move and I haven’t yet worked out how to delete it if it doesn’t have a name, hence zombie.

• Click in cell and select Insert > Shape > Basic Shapes > Rectangle.
• Draw the rectangle inside the cell and colour it white.
• Important Right-click rectangle and select Name, give it a name so you can select it from Navigator if you need to edit or delete it.
• Right-click it and select Edit hyperlink and enter your link, OK.
• Right-click on rectangle and select Arrange > To Background
• Enter anything you like in the cell and it retains its hyperlink

In sample ods cell B2 has hyperlink.
HyperlinkShape.ods (8.8 KB)

[erAck]

There can be only one cell content a time, so logically any text that happens to be a hyperlink is replaced if you enter another value like 23. However, you can construct a hyperlink using the HYPERLINK() function in a formula expression sa cell content, so in for example A1 have

=HYPERLINK("https://example.com/";B1)

which produces a clickable hyperlink of its first argument and displays the second argument, here whatever is in B1, i.e. 23

[Alexutzu]

In the excel file created with MS Office, it was possible to have any data written inside the cell, and apart of that a hyperlink to wherever I need it to send upon ctrl+click, as the ss attached (hopefully it’s loading it along with the message). for example, I was able to modify the number inside the cell, but the hyperlink remains unchanged/deleted. From @erAck above message I understood that this is not (yet) possible with Libre. Is that correct?

[erAck]

@Alexutzu

as the ss attached

There is no document attached. Try again, or better edit your question and attach there.

But maybe I misunderstood, it’s not clear to me what you are actually trying. If you want to change the display value of a Ctrl+K hyperlink inserted then select the hyperlink within the cell and press Ctrl+K again to edit the Text value. Or if the hyperlink is the only cell content then just hit Ctrl+K on the cell.

[Lupp]

The question isn’t completely clear to me, but if the cell to be linked actually needs to be editable in any way, a solution isn’t supported by the UI as far as I know. There may be good reasons.

However, each cell object has a property .Hyperlink. If you assign a string to it, LibO Calc will try to use it as a hyperlink if you activate the call (Ctrl+Click mostly). This doesn’t work for a blank cell, but only for the used part of the cell’s area if it has any content. (Empty string also creates a small linked area in a similar way asd a HYPERLINK() call with an empty string as the second argument would do.)

User code to make the feature available is contained in the attached example and assigned to the onExecute of a button…
Warning: I would suspect this feature to be potentially dangerous.
cellLinksMayBeDangerous.ods (13.1 KB)