Solved

Using Libreoffice HYPERLINK function does not work anymore as before. Has it been changed?

Posted on 2014-03-01
5
1,646 Views
Last Modified: 2014-03-12
It seems the HYPERLINK function in LibreOffice Calc has been changed. Since several years I use a spreadsheet containing a summarizing worksheet to summarize several worksheets in the same file. The HYPERLINK formula is

=HYPERLINK(CHAR(35)&C60&".A"&MATCH(B60;INDIRECT(CHAR(39)&C60&CHAR(39)&".$A$1:$A$200");0);"GoTo")

in which column B contains a year ("2013") and column C the name of the worksheet which is summarized in various fields/columns in that row. With 'Ctrl'-click I can jump to the first row of the referenced year in the referenced worksheet.

When a new year has to be added I simply copy the rows containing all summarizing formulas. The formula mentioned above becomes

=HYPERLINK(CHAR(35)&C71&".A"&MATCH(B71;INDIRECT(CHAR(39)&C71&CHAR(39)&".$A$1:$A$200");0);"GoTo")

Unfortunately the new (i.e. just copied function) function does not work now: 'Ctrl'-click does nothing, no pop-up window, no error message or whatsoever. Simply, nothing happens. In addition, the cursor which changes when hovering above a cell containing the hyperlink function does not change in the newly copied cell as it does change in the existing cells where 'Ctrl'-click functions as expected.

I have tried to change Libreoffice Options settings, but no success.

How can I get a working 'Ctrl'-click and jump to a certain cell in another worksheet in the same (spreadsheet)file?

In addition I create a simple Calc spreadsheet to see if there is indeed something going on. I have attached the file ("testhyper.ods").

Cell Sheet1.A1 contains the formula =HYPERLINK(Sheet2.A1;"LINK")
Ceel Sheet2.A10 contains a formula evolving to the string "Sheet2 cell A10" [formula is a very simple one "=LEFT(A9;FIND("A";A9))&ROW(A10)"]

The cursor changes when hovering above the cell Sheet1.A1 containg the hyperlink formula. When using 'Ctrl'-click an error message window pops up:

"Sheet2 cell A10" is not an absolute URL that can be passed to an external application to open it.

(un)Checking the "Save URLs relative to" both filesystem and internet settings (Tools - Options - Load/Save - General)does not any effect at all.

My system consists of Windows 8.1 (desktop mode), 6GB RAM and Libreoffice 4.2.2.1. I encountered these problems using version 4.2.1

Any help is appreciated.

Regards
Steamingon
testhyper.ods
0
Comment
Question by:Steamingon
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39897450
I couldn't get it to work either.  I tried to put a new hyperlink in there and that 'worked' on the third try by naming the range on Sheet2 but then Sheet2 got corrupted.
0
 

Author Comment

by:Steamingon
ID: 39897822
I appreciate your attempt(s). Do you agree something is going on?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39897874
It did appear to be odd behavior.  The 'hyperlink' dialog didn't look like it was intended for sheet to sheet links.
0
 

Accepted Solution

by:
Steamingon earned 0 total points
ID: 39900336
The first part of my question, related to the HYPERLINK function in a summary worksheet can be resolved (if one likes to call it a "solution")  by putting the string concatenation i.e. cell reference string CHAR(35)&C71&".A"&MATCH(B71;INDIRECT(CHAR(39)&C71&CHAR(39)&".$A$1:$A$200");0)

in another cell on the same row and reference that particular cell in the HYPERLINK function

=HYPERLINK (ZZ71;"GoTo")

I do not want to call it a solution, but "It works".


The second part of my question, using a small test spreadsheet to see what happens, I was able to solve by adding the filename to the hyperlink function, either  as part of a concatenation string or explitcitly. See attached file "testhyper_2.ods"

"It works"
testhyper-2.ods
0
 

Author Closing Comment

by:Steamingon
ID: 39922900
No other solution was added.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question