Bug 128881 - Conditional Formats sometimes evaluated wrongly when using Named Ranges
Summary: Conditional Formats sometimes evaluated wrongly when using Named Ranges
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.0.0.alpha1+
Hardware: All Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2019-11-18 18:38 UTC by Jim Avera
Modified: 2021-06-26 04:00 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
t.ods (test spreadsheet -- see STEPS TO REPRODUCE) (69.49 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-11-18 18:39 UTC, Jim Avera
Details
t.ods (test spreadsheet -- see STEPS TO REPRODUCE) (69.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-11-18 18:41 UTC, Jim Avera
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jim Avera 2019-11-18 18:38:38 UTC
Description:
If a Conditional Format has multiple conditions, and the condition formulas refer to a cell using a Named Range, then (sometimes) a condition does not match when it should.   

There might be a race condition between evaluating Conditional Format conditions, translating Named Ranges, and/or storing new values when a cell is modified.

Please see the attached spreadsheet, which contains detailed instructions.


Steps to Reproduce:
1. Load the attached "t.ods" spreadsheet, with macros DISABLED.
2. Place cursor in cell A2
3. Press the DEL key

Actual Results:
RESULTS: Cell B2 is displayed with the wrong Style

Expected Results:
Condition #1 in the multi-condition Conditional Format should match, and Conditions #2 and #3 should not be evaluated at all.  However the purple style comes from Condition #3, so for some reason Condition #1 is not matching or is ignored.


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Jim Avera 2019-11-18 18:39:22 UTC
Created attachment 155928 [details]
t.ods (test spreadsheet -- see STEPS TO REPRODUCE)
Comment 2 Jim Avera 2019-11-18 18:41:34 UTC
Created attachment 155929 [details]
t.ods (test spreadsheet -- see STEPS TO REPRODUCE)
Comment 3 Oliver Brinzing 2019-11-18 19:25:08 UTC
(In reply to Jim Avera from comment #0)
> If a Conditional Format has multiple conditions, and the condition formulas
> refer to a cell using a Named Range, then (sometimes) a condition does not
> match when it should.   

Thank you for reporting the bug. 

I had a look at the two named ranges:

Shading -> $A:$A
SecType -> $B:$B

Is this coerrect?

Can you please check, what happens if you set the ranges $A$2 and $B$2 ?

I have set the bug's status to 'NEEDINFO'. 
Please change it back to 'UNCONFIRMED' once the requested info is provided.
Comment 4 Jim Avera 2019-11-19 02:13:06 UTC
> what happens if you set the ranges $A$2 and $B$2 ?

I don't know, but that would be incorrect for the real application (where there are many rows with the same Conditional Format, not just row 2).
Comment 5 QA Administrators 2019-11-19 03:29:30 UTC Comment hidden (obsolete)
Comment 6 m_a_riosv 2021-06-25 14:59:41 UTC
Maybe I'm missing something, but third condition is:
STYLE(IF(Shading="";"WhiteOnMagenta";VLOOKUP(Shading;StyleTbl;2;0)))
Delete A2 -> Shading="", so WhiteOnMagenta style is applied.
Comment 7 Jim Avera 2021-06-26 04:00:02 UTC
@m.a.riosv -- That would be the case if Condition #3 was the only condition, but Condition #1 should match after deleting the content of A2.

---

However, the problem seems to be gone in a recent master build.   So I will close this as WFM using

Version: 7.2.0.0.alpha1+ / LibreOffice Community
Build ID: 0bcc06b0a563da08ccf1704b2f51376f27f51f62
CPU threads: 12; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2021-05-23_18:42:59
Calc: threaded