HYPERLINK does not work when made conditional

[fatbeard1]

I have a (rather large) collection of surplus sales items.
To keep track of these, I have them grouped in folders with photos, descriptions etc.
The name of the folder is a cell in column D of my spreadsheet, the formula
=HYPERLINK(CONCATENATE("/home/leo/CO in-verkoop/",LEFT(D31,4),"/",MID(D31,5,2),"/",D31)
in column L produces a workable link to that folder.

In another tab of that spreadsheet, not all lines have a valid folder name in column D; so I want the link to only show when column C is a zero-length string and column E is non-empty.
Yet when the formula is embedded in the IF function
=IF(AND(C31="",NOT(ISBLANK(E31))),HYPERLINK(CONCATENATE("/home/leo/CO in-verkoop/",LEFT(D31,4),"/",MID(D31,5,2),"/",D31),"link"),"")
the hyperlink does not work at all. The cell shows the text “link” as a hyperlink, but hovering over it and CTR-click both do nothing…

Is this a (known) bug or am I doing something wrong here?
More to the point: how do I get this to work properly?

Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 6; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.10
Calc: threaded

[sokol92]

Since the first versions of Excel spreadsheets, the Hyperlink function has been used to create shortcuts for jumping to a specific location in the current document or to an external link. Typically, it’s the outermost function in a formula.
Let’s try the following experiment in Calc.
Enter the following formula in cell B1:

=IF(A1=""; "Empty"; HYPERLINK("https://google.com"; "Search"))

Formula value: Empty.
Hover the mouse over this text in the cell and wait. A tooltip appears suggesting that you can follow the hyperlink by pressing Ctrl + click.
Excel works the same way.

I don’t consider this a bug. Hyperlink should always be used as an outermost function.

[ady]

@fatbeard1

Please try the following formula (based on what you posted):

=HYPERLINK(REPT(CONCATENATE("/home/leo/CO in-verkoop/",LEFT(D31,4),"/",MID(D31,5,2),"/",D31),N(AND(C31="",NOT(ISBLANK(E31))))),"link")

[Lupp]

@fatbeard1

...addresses a point that is not mentioned...

The relevant criticism should concern the posts of the OQer:

• Needed information missing
• No example file
• No previous attempts (seemingly) to find the cause of the problem.

LibO 25.8.4.2 on Win10

I attach a .ods file containing a sheet with more details and a very short “tutorial” about finding errors in complex formulas.
I do not think that this topic is solved the way the OQer assumes.
HowToFindErrorsInComplexFormulas_originally_disask_134611.ods (37.4 KB)

I think helpers wasted a lot of time. Comments welcome.

[fatbeard1]

It may not have been abundantly clear that the formula in question worked fine, yet somehow stopped working for a while without any changes made to the computer, OO version, or spreadsheet formula or contents.

@Lupp

No example file

The percieved problem came by itself and also went away by itself; it could afterwards not be reproduced. That makes producing a example file kinda difficuilt and lead me to the conclusion that the phenomenon (non-responsive link) was produced by an over-busy piece of software.
For me this is no longer an issue (if it ever was one), so nobody needs to put any (further) time into it.
Should it re-appear, I’ll exercise some more patience and live with it for as long as the phenomenon lasts.

In the example file @Lupp questions several things, let me elaborate on some of them.

• Delimiter usage: comma or semicolon. I am in the Netherlands where the comma is the decimal delimiter although I prefer a point, so I set that in my system preferences. Apparently OO chooses some things from the locale settings, others from the system preferences. Soit, I learned when to use what.
• The only ‘lead’ to the non-reproducable phenomenon was the formula itself (used in well over 1000 instances); in order to preserve any possible faulty details it was copy-pasted from the entry window. Whatever the content of the cells in the D column, the tooltip should reveal the link when hovring over it. That did not happen, hence the whole topic.
• To @Lupp, the use of CONCATENATE may be less clear than using several anpersands, to me it is the other way round. A matter of taste, I guess.
• That AND(C31=""; NOT(ISBLANK(E31))) can only be TRUE or FALSE is the whole point: I do not want the link to appear if the conditions are not met. When the link appears, it functions now (without any modification to the formula!).
• I already said that there were a LOT of items, each in its own folder. These folders are grouped by year, then by month and are individually named with the date the item was introduced to the world, with a sequence number attached with a hyphen. That whole structure looks like this (yes, I have selected to show folders before files):

This makes navigating the huge amount of photo’s and texts (total 9800 items) managable…
I admit that the lowest level could just be a sequence number, but this way I can recover folders that get inadvertantly misplaced.

[fatbeard1]

@sokol92 addresses a point that is not mentioned anywhere in the documentation; frankly, if the correct working of any function depends on it being the outermost one that should be mentioned in -at least- the help files.

As it turns out, my usage of the HYPERLINK function is OK: although I observed the problem before the original formula now works as expected again. I will write it down as a product of internal housekeeping being too busy with whoknowswhat…

The solution that @ady provided keeping HYPERLINK as outermost function does work, although I find it less intuitive…

[Hrbrgr]

@fatbeard1

addresses a point that is not mentioned anywhere in the documentation;

Writer Guide 26.2 - Chapter 3, Working with Text: Advanced

[ady]

@fatbeard1

As it turns out, my usage of the HYPERLINK function is OK: although I observed the problem before the original formula now works as expected again.

Having read other comments that have been posted (chronologically) before this one, probably this comment is no longer relevant, but anyway… I don’t really understand what exactly you mean with that phrase I just quoted. What exactly was working, then (inexplicably) started to have some “problem”, and now it (inexplicably) works correctly again?

[fatbeard1]

The formula as I posted it in my opening post worked as expected: a working link when required, an empty string when not.
For unknown causes this stopped working: no tooltip, no working link; I spent quite a bit of time trying to identify those causes, to no avail. Since the name of the folder is in the spreadsheet, I worked with Nautilus to get to the data; a bit cumbersome, but doable.

At some later date, all worked again as expected; I blamed the original failure on some behind-the-scenes update from some package (Phyton? Java? No idea, OO itself was not updated) and gave it no further thought.
A few days ago, the phenomenon repeated itself: no tooltip, no working link. After a few iterations I discovered that removing the ‘IF’ part of the formula returned the functionality. That not being a viable solution lead me to post the question “Is this a (known) bug or am I doing something wrong here?”.

@sokol92 suggested that the HYPERLINK function should be the outermost function (for which there was no documented basis at the time), which likely prompted @ady to produce his formula.
While experimenting with his formula, I discovered that my original formula had returned to a working state; I have no idea by what mechanism that happened but was greateful that it had: it saved me a lot of work.
Because the original formula was working again (-- checking – still is! ) I assumed that my interpretation -that the HYPERLINK function should not necessarily be the outermost function- is correct. That, in turn, lead to the quoted phrase…

During the whole discussion, @Hrbrgr boarded the train and added a note to the wiki page for the -alledged- necessity of HYPERLINK being the outermost function, which may or may not be true…

[fatbeard1]

Nice find, but that is about a Writer document and I’m talking about a Calc spreadsheet…

[Hrbrgr]

@fatbeard1

about a Calc spreadsheet

Calc Guide 26.2 - Chapter 12, Linking Data

[fatbeard1]

Also a nice find, but it does not describe the hyperlink function in any detail…
A better link would have been The Document Foundation’s wiki page on the Hyperlink function, but that also does not mention anywhere that the function should be the outermost one in a formula…

[Hrbrgr]

@fatbeard1

A better link would have been The Document Foundation’s wiki page on the Hyperlink function

… that the function should be the outermost…

done!

Thanks!

[ady]

@Hrbrgr,

The concept that the HYPERLINK() function ought to be the outermost function of the formula is inaccurate.

What is actually happening is that a tooltip hint will be displayed when hovering over the cell, just because it contains (somewhere) the HYPERLINK() function. But the function itself is working correctly (and so is the [CTRL]+[CLICK] feature), even when it is not the outermost function of the formula.

If you would be so kind, please, either improve the statement that you added to the wiki, or delete it so users won’t get the (incorrect) impression that there is only one way to use the HYPERLINK() function.

Thank you in advance.

[sokol92]

@ady

What is actually happening is that a tooltip hint will be displayed when hovering over the cell, just because it contains (somewhere) the HYPERLINK() function.

The described formula behavior is undoubtedly misleading to the user (incorrect link). Therefore, such formulas should be avoided.

[ady]

@sokol92

@ady

What is actually happening is that a tooltip hint will be displayed when hovering over the cell, just because it contains (somewhere) the HYPERLINK() function.

The described formula behavior is undoubtedly misleading to the user (incorrect link). Therefore, such formulas should be avoided.

As I said, that’s inaccurate. For instance, you could have an IF() with 2 different links, one for the true condition and another for the false one. Both results should work correctly.

Moreover, the case originally presented in this topic also works “as the user expects”.

Having a Note in the wiki that somehow implies that the function will fail unless it is the outermost is slightly misleading, and could easily prevent possible formulas that would be adequate for the user’s needs.

A failure of a function means that the result is incorrect. For instance, if a function does not accept array arguments unless the function is introduced as an array formula implies a possible incorrect usage of that function.

OTOH, using HYPERLINK() as an inner function in a formula is not completely unacceptable nor incorrect (as the OP demonstrates by his own statements).

Therefore, @Hrbrgr, the Note in the wiki should rather be improved or eliminated.