Calc hyperlinks stop working when edited

[Pheeble]

I have a Calc document (created last year, but I’m currently running LibreOffice 6.0.3.2 on Linux Mint 18) containing multiple sheets. One sheet contains hyperlinks to cells in other sheets so I can jump to the relevant cell. The hyperlink cells use a function eg. ‘=HYPERLINK("#2017_Itemised.E1",“Fuel”)’ and they work perfectly until I have to edit them. The example given displays in the cell as ‘Fuel’ and if I Ctrl-click on it the view jumps to cell E1 in sheet ‘2017_Itemised’.

When I edit the hyperlinks they stop working. The above example cell then displays the formula as text, and it no longer works as a hyperlink. I have checked and rechecked, and the edited link address is perfectly valid. This problem occurs even if I don’t change the link address - such as by replacing ‘2017’ with ‘2017’ in the example.

The really weird thing is the first hyperlink in the sheet can be edited without any problem. Every other hyperlink in the sheet breaks when edited.

By using ‘Ctrl+F8’ to view the cell format types I can see that the format of the faulty edited hyperlink cells changes from green (ie. a formula) to blue (text).

What is causing this? More importantly, how can I get Calc to stop doing this, and how can I change the cell format back to Formula so the hyperlink works again?

[Pheeble]

I stumbled over a work-around for this myself.

I had to delete the contents of the cell containing the ‘=HYPERLINK…’ function, use the ‘Clone Formatting’ brush to copy the cell format from a cell containing a working hyperlink to the now empty cell, and enter the hyperlink function again, at which point Calc finally accepted it and displayed it as a hyperlink.

This looks like yet another bug, or perhaps something broke when I upgraded LibreOffice from version 5 to version 6.

[erAck]

Sounds like the cell had a number format of type Text assigned, which forces any input in a cell to text content, no matter if it could be interpreted as numeric or formula. It’s not a bug, it’s a feature and the same in other major spreadsheet applications.

[Pheeble]

As indicated in the orginal question, the hyperlink was working perfectly until the link address was edited, and it was obviously formatted correctly until that point. LibreOffice messed up the cell format when the link address was edited. This is not standard behaviour in any application. It is not a feature, it’s a bug. Please take the time to read and understand the question before posting.

[erAck]

Please take the time to understand the comment before discrediting.

Calc does not change a cell’s number format to type Text when editing a formula. Proof:

0. ensure a cell has the General number format assigned
1. enter a HYPERLINK function
2. verify the cell still has General number format
3. edit the hyperlink
4. verify General format

What you encountered was

1. HYPERLINK function entered
2. somehow applied Text number format to the cell
3. => all further input will be text