Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oxygen Office error 1305 on vista 2 669
Is there a PowerPoint Viewer for Mac OS X 7 1,876
OpenOffice: trying to add and delete text 1 434
add module to calc 32 423
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now