Download it now!
Bug 119715 - FORMATTING - Conditional formatting based on formula doesn't affect current but differnt row
Summary: FORMATTING - Conditional formatting based on formula doesn't affect current ...
Status: RESOLVED DUPLICATE of bug 119178
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
Reported: 2018-09-05 22:33 UTC by FS
Modified: 2018-09-06 13:20 UTC (History)
1 user (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 FS 2018-09-05 22:33:20 UTC
When defining a conditional formatting rule, a formula containing a relative cell reference will be changed automatically, breaking the formatting rule. The behaviour depends if the cell range is selected before calling the Conditional Formatting dialogue, or when the cell range is set manually in the rule definition dialogue

Steps to Reproduce:
1. Create a new calc sheet
2. Select Menu Format -> Conditional formatting -> Manage
3. Click Add
4. Under Condition 1, select "Formula is"; as formula, enter '$G3="L"'; select a style sheet to apply if formula is true;
5. In the "Cell Range" field, enter "A3:F25"
6. Click OK in both dialogues.

Actual Results:
The Value of the cell in the G column doesn't affect the current row but (current row - 2). When checking the conditional formatting rules, it appears that the formula applied is now '$G5="H"'.

Expected Results:
The value of the G column should affect the current row. The formula shouldn't change without user interaction...

Reproducible: Always

User Profile Reset: No

OpenGL enabled: Yes

Additional Info:
After Step 1 above, select the range A3:F25. Then proceed with Steps 2-4, omit step 5, continue with step 6.

Result: Actual result == expected result. So it matters if the range is "pre-selected" before calling the conditional formatting dialogues.

tested with:

Build ID: 1:5.1.6~rc2-0ubuntu1~xenial4
CPU Threads: 4; OS Version: Linux 4.15; UI Render: default; 
Locale: de-DE (de_DE.UTF-8); Calc: single

Another Modification: With Version: (x64)
Build-ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
CPU-Threads: 3; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: group
the behaviour is different again. Steps to reproduce will lead to an immediate modification of the formula in step 6 after first "OK" - formula is now '$G2="L"' and will affect (row +1) instead of current row. But here again, it works if the range is selected before step 2.
Comment 1 Roman Kuznetsov 2018-09-06 09:31:17 UTC
confirm in

Build ID: 2718b4a18dfcc6a54ebe5f7b801ee7a47fa81e0c
CPU threads: 4; OS: Windows 6.1; UI render: default; 
Locale: ru-RU (ru_RU); Calc: group threaded

really changes address from $G3 to $G5 when push OK and this shows in reopened dialogue Manage Conditional formatting

If I change address back to $G3, then condition works fine now
Comment 2 FS 2018-09-06 10:04:53 UTC
Minor correction to my initial description: under "Actual result", the resulting formula in my example is '$G5="L"', not "H" - only the cell reference gets modified, not the string value it contains.
Comment 3 Katarina Behrens (CIB) 2018-09-06 13:20:34 UTC

*** This bug has been marked as a duplicate of bug 119178 ***