Bug 74967 - Allow rule for "no value (entered)" in conditional formatting
Summary: Allow rule for "no value (entered)" in conditional formatting
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Conditional-Formatting-Editing
  Show dependency treegraph
Reported: 2014-02-14 08:53 UTC by Ulrich Windl
Modified: 2018-07-01 08:58 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Test Case (15.34 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-09-04 06:20 UTC, Ulrich Windl

Note You need to log in before you can comment on or make changes to this bug.
Description Ulrich Windl 2014-02-14 08:53:55 UTC
Currently, if you have a conditional formatting rule "equals 0", all fields with a true zero-value, or fields without a value trigger the rule.
It is not possible to add a rule that triggers on "no value". It should be possible, however.
Comment 1 m_a_riosv 2014-02-15 19:21:04 UTC
Hi Ulrich, thanks for reporting.

Please could you attach a sample file with issue.
Comment 2 QA Administrators 2014-09-03 21:32:46 UTC Comment hidden (obsolete)
Comment 3 Ulrich Windl 2014-09-04 06:20:14 UTC
Created attachment 105717 [details]
Test Case

Sorry, first I thought the issue is so simple that a test case isn't actually needed, but then I lost that bug in the personal radar. Anyway here's a test case where you see that cells with no value are treated the same as those with value zero when the condition is "equals zero".
Comment 4 lp1 2014-09-09 10:19:50 UTC

Since Ulrich provided a sample file, I think this should be changed to UNCONFIRMED.
Comment 5 ign_christian 2014-09-09 16:44:01 UTC
Agreed..this could be a useful feature to differentiate empty cell (no value) and zero value within CF.
Comment 6 Regina Henschel 2018-03-08 22:35:19 UTC
Set cell cursor on Cell A1. Define condition as: Condition type "Formula is" with formula "AND(A1=0;NOT(ISBLANK(A1)))" or with formula "IF(ISBLANK(A1);FALSE();A1=0)" on range "A1:A7".
Because the address A1 is relative it adapts for the other cells of the range to e.g "AND(A7=0;NOT(ISBLANK(A7)))" for cell A7.

Unfortunately the ODF part 2 has in section "6.3.5 Conversion to Number" the rule:
"If the expected type is Number, then if value is of type Reference ... If the cell is empty (blank), use 0 (zero) as the value. ..."
The comparison with operator = and one value 0 expects a number from the reference A1. So getting "A1=0 return false for an empty cell A1" seems only possible, if not using namespace "of:".
Comment 7 Roman Kuznetsov 2018-07-01 08:58:21 UTC
You can use condition as <Cell value - equal - ""> and style Default for it to resolve your problem when value in cell equal zero. This condition should be first in list of conditions for your range