Bug 119178 - calc: conditional formatting formula refers wrong cell
Summary: calc: conditional formatting formula refers wrong cell
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium normal
Assignee: Vasily Melenchuk (CIB)
Whiteboard: target:6.2.0
: 119715 (view as bug list)
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
Reported: 2018-08-09 14:32 UTC by Vasily Melenchuk (CIB)
Modified: 2019-08-08 08:33 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Conditional formatting testcase (11.14 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-08-09 14:33 UTC, Vasily Melenchuk (CIB)

Note You need to log in before you can comment on or make changes to this bug.
Description Vasily Melenchuk (CIB) 2018-08-09 14:32:23 UTC
Conditional formatting formula uses relative references to cursor and not to the cell applied.

Steps to Reproduce:
1. Open attached spreadsheet
2. Ensure that cursor is located NOT in B15 cell
3. Open Menu->Format->Conditional->Manage...
4. Click "Add.."
5. Select type "Formula is" with formula $A15 = "test"
6. (optional) Apply style "Error"
7. Select cell range $B$15
8. Press "OK"

Actual Results:
In conditional formatting list element is displayed with condition $A26 = "test"
(can be different from $A26) and once conditional formatting dialog is closed, B15 cell is not highlighted as expected.

Expected Results:
Formula should be same as inserted ($A15 = "test"), one dialog is closed cell B15 is highlighted with red.

After edit this formula with same value it is used correctly.

Reproducible: Always

User Profile Reset: No

Additional Info:
Comment 1 Vasily Melenchuk (CIB) 2018-08-09 14:33:15 UTC
Created attachment 144064 [details]
Conditional formatting testcase
Comment 2 Mike Kaganski 2018-08-09 14:56:55 UTC
I was under impression that this is as designed. If not, then what cell in the range to use as reference, if you define not a single-cell range? top left?
Comment 3 Vasily Melenchuk (CIB) 2018-08-09 17:39:54 UTC
Mike, in mot cases (when you add conditional formatting to the cell under cursor) everything is ok. But if current cell and cell for conditional formatting are different, things are strange. In this situation (as described in test scenario) cell under cursor should have no influence on formula. 

And yes, use  top-left cell for range looks logical in my mind.
Comment 4 m.a.riosv 2018-08-10 00:23:49 UTC
Maybe this https://bugs.documentfoundation.org/show_bug.cgi?id=54774#c3 could bring some light.
Comment 5 Katarina Behrens (Inactive) 2018-09-06 13:20:34 UTC
*** Bug 119715 has been marked as a duplicate of this bug. ***
Comment 6 Thorsten Behrens (CIB) 2018-09-21 12:42:15 UTC
WIP patch is here: https://gerrit.libreoffice.org/58800
Comment 7 Commit Notification 2018-11-14 12:36:27 UTC
Vasily Melenchuk committed a patch related to this issue.
It has been pushed to "master":


tdf#119178: use current range as a range for conditional format

It will be available in 6.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:

Affected users are encouraged to test the fix and report feedback.
Comment 8 Xisco Faulí 2018-12-18 15:35:39 UTC
A polite ping to Vasily Melenchuk:
Is this bug fixed? if so, could you please close it as RESOLVED FIXED ? Otherwise, Could you please explain what's missing?