Solved

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

Posted on 2014-03-01
5
1,708 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Not everyone has adapted to a rapid advancement in technology; there are people who are reluctant or afraid to delve into this brave new world of IT. If you have a friend or a family member who suffers from the so-called technophobia, here is how yo…
Check out this step-by-step guide for asking an anonymous question on Experts Exchange.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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