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.
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"'.
The value of the G column should affect the current row. The formula shouldn't change without user interaction...
User Profile Reset: No
OpenGL enabled: Yes
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.
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: 184.108.40.206 (x64)
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.
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
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.
*** This bug has been marked as a duplicate of bug 119178 ***