Solved

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

Posted on 2014-03-01
5
1,736 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

Technology Partners: 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

This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
Multi-threading long-running processes can have a significant increase in overall performance and drastically decrease over time it takes for a process to complete. Unfortunately, not all applications support native multi-threading, some by design a…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

724 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