LibrOffice Calc : Adding Hyperlink to Cell Containing a Formula
I’m attempting to migrate my Excel spreadsheets over to LibreOffice Calc but I’m having difficulty with the cells which contain a numeric value or formula (e.g. price * VAT) AND a hyperlink (e.g. to the supplier’s website).
In Excel these cells can still be used for calculations e.g. =SUM, or to show price including sales tax (value*1.20), in addition to being hyperlinked.
Calc doesn’t seem to be able to do this and I have found two major problems trying to use the “price” cells:
Am I missing something fundamental here? I’m using the current version (4.4.0.3) on a Win7 PC.
THE DETAILS :
-
When the Excel spreadsheets are opened in Calc, everything looks and and seems to function ok, however subsequent editing is problematic.
a) The original URL hyperlink cannot be edited because the Hyperlink dialogue box opens with no values.
b) You cannot select “price” cells with left mouse-click. You have to click on any unused/empty cell then navigate to the price cell using the keyboard arrow keys, then edit the price or formula using the Input Line. -
Creating a hyperlinked “price” cell in Calc doesn’t seem possible because:
a) Applying a hyperlink to a cell appends existing content with the URL text, therefore is no longer a value.
b) Applying a hyperlink to a value in the cell (double-click cell and highlight the value) adds the hyperlink but again results in text. Thus the cell cannot be used in any calculations.
c) Applying a hyperlink to a formula in a cell e.g. =15.6*1.2 simply doesn’t work.
Here’s a simple spreadsheet to illustrate what I mean. It was created as an Excel.xls then opened in Calc and re-saved as .ods
The only workaround I can see is to insert an extra column alongside each price column just to contain the hyperlink. However this would involve editing EVERY price entry which is not feasible.