Bug 75820 - EDITING: IF function with HYPERLINK as Then_value causes HYPERLINK also on Otherwise_value
Summary: EDITING: IF function with HYPERLINK as Then_value causes HYPERLINK also on Ot...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2014-03-06 05:55 UTC by panoworks
Modified: 2015-02-11 21:20 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description panoworks 2014-03-06 05:55:06 UTC
=IF(FALSE,HYPERLINK("http://freedesktop.org/","freedesktop.org"),"this should not be a link")

Causes a hyperlink to be inserted with both URL and text "this should not be a link".

The context in which I use this is where there's a text and a URL field, and only if the URL field is filled in, the cell with the formula will have a hyperlink - otherwise just the text.
Comment 1 m.a.riosv 2014-03-06 23:25:41 UTC
Hi panoworks, thanks for reporting.

I think it's not possible use HYPERLINK() in that way.
Help have:
When you click a cell that contains the HYPERLINK function, the hyperlink opens.

On other hand, if FALSE is not defined as range name, the function is FALSE().

If you want a workaround, a second hyperlink with a referencing the proper cell where the hyperlink is, does nothing:
=IF(FALSE();HYPERLINK("http://freedesktop.org/";"freedesktop.org");HYPERLINK("#"&CELL("address");"this should not be a link"))
Comment 2 panoworks 2014-03-07 09:05:45 UTC
FALSE vs FALSE() doesn't really seem to make a difference (again: in my use case I check if the URL cell is empty or not, and intend to only insert a hyperlink if the URL cell is not empty)

Your suggested work-around is interesting, though I guess it really just sets up a hyperlink to itself, rather than no hyperlink :)

The question is, if the definition is as follows,
"When you click a cell that contains the HYPERLINK function"
then does =IF(FALSE(),HYPERLINK("http://freedesktop.org/";"freedesktop.org"),"not a link") mean that the cell contains the hyperlink function?

On the one hand, the function is certainly used in the formula, so yes.
On the other, the formula suggests that the function is not what actually gets used for the cell's content, so no.
Comment 3 QA Administrators 2014-10-05 23:05:41 UTC
Dear Bug Submitter,

This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information.

For more information about our NEEDINFO policy please read the wiki located here: 

If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed.

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team
Comment 4 panoworks 2014-10-06 20:50:29 UTC
switching to UNCONFIRMED (although I guess m.a.riosv sort-of confirmed it).  All the required information is in the post.  The question is whether in the case of an IF+HYPERLINK combination, the rules for an IF get precedence, or whether the rules for a HYPERLINK get precedence.  I'm obviously of the view that the former should apply.  Either which way, it would be good to clarify somewhere (documentation, 'known issues', whatever) - even though I realize that this is probably not a very oft-used construction :)
Comment 5 Jan 2015-02-10 14:50:45 UTC
Verified in Libreoffice Version: Build ID: 420m0(Build:2) on Ubuntu 14.04 LTS.

This clearly indicates a bug or to be more specific: undefined/unwanted behaviour. However, there are at least two ways to fix it:
1. Implement/Fix conditional hyperlinks
2. Forbid hyperlinks inside an if. I would prefer this over just documenting it.

Question: Are conditional hyperlinks a feature you need? Or would 2 do it for you?

I will switch this to NEW since it actually is buggy. How to solve it would be another issue to discuss.
Comment 6 Jan 2015-02-10 15:42:12 UTC
Looks like this is bug compatible with MS Excel: http://stackoverflow.com/questions/11363263/excel-conditional-hyperlink-outputs-the-hyperlink-for-both-conditions

Therefore, option 2 would probably cause problems. But 1 should still work.

I looked at the code. Unfortunately, the hyperlink is set when parsing the formula and not when evaluating it (formula/source/core/api/FormulaCompiler.cxx line 1209).
Comment 7 panoworks 2015-02-10 15:47:41 UTC
Ah bugger, lost my reply ( could not serialize access due to concurrent update ), but tl;dr(ewrite):
I've since taken to a different approach.  This was mainly as a convenience factor for people to have a clickable link on a company name if the company indeed had a website.

I still think fixing it (Excel compatibility aside) is a better goal, but I understand that the code path may need rigorous changes for something that's likely not used very often.
Comment 8 Jan 2015-02-11 18:47:19 UTC
Apparently this feature was designed to be bug-compatible to MS Excel: http://www.openoffice.org/specs/calc/compatibility/HyperLinkFunction.sxw

The following will magically work and turn into a link in excel: =IF(0;HYPERLINK("a","b");"http://documentfoundation.org/");

Therefore, this is indended but, unfortunately, broken behaviour. Setting to Resolved - not our bug. If upstream (=Microsoft) fixes this, we also will.
Comment 9 panoworks 2015-02-11 21:20:20 UTC
The underlying rationale is thoroughly disappointing, but I understand.