Example: =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.
Hi panoworks, thanks for reporting. I think it's not possible use HYPERLINK() in that way. Help have: " HYPERLINK 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"))
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.
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: https://wiki.documentfoundation.org/QA/FDO/NEEDINFO 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
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 :)
Verified in Libreoffice Version: 4.2.7.2 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.
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).
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.
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.
The underlying rationale is thoroughly disappointing, but I understand.