Ask Your Question
0

Libre Calc: Cannot Add a Hyperlink to Cell Containing a Formula

asked 2015-02-21 20:40:43 +0200

AllanR gravatar image

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 :

1) 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.

2) 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 https://www.dropbox.com/s/04rn6niabmt...

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-02-21 22:01:05 +0200

karolus gravatar image

Hallo

Use the Calcfunction =HYPERLINK("some URL"; SUM(B1:D1) )

edit flag offensive delete link more

Comments

Thanks Karolus, that formula works well - it also accepts a Named area, and permits the hyperlinked cell to be used in other formulas.

AllanR gravatar imageAllanR ( 2015-02-22 21:59:40 +0200 )edit

I've subsequently raised a bug report Bug 89633 and enhancement request bug 89572 to hopefully allow CALC to handle Excel hyperlinked cells properly.

AllanR gravatar imageAllanR ( 2015-02-25 21:46:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-02-21 20:40:43 +0200

Seen: 1,058 times

Last updated: Feb 21 '15