• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1888
  • Last Modified:

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

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
Steamingon
Asked:
Steamingon
  • 3
  • 2
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
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
 
SteamingonAuthor Commented:
I appreciate your attempt(s). Do you agree something is going on?
0
 
Dave BaldwinFixer of ProblemsCommented:
It did appear to be odd behavior.  The 'hyperlink' dialog didn't look like it was intended for sheet to sheet links.
0
 
SteamingonAuthor Commented:
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
 
SteamingonAuthor Commented:
No other solution was added.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now