[Solved] Conditional Formatting formula not updating

[Acidello]

Good day to all Openoffice users!

I have searched for this issue but I found only similar ones, not exactly the same.

I have a conditional formatting on cell B2 with formula "A1>0" (B2 is displayed in red if A1>0).
If I move (cut and paste) cell A1 to another place (on the same sheet) the conditional formatting will still refer to A1.
I didn't move the cell with conditional formatting enabled, but only the one appearing in the formula. Everything is as I wrote, no "$" involved.
I thought conditional formatting formulas followed the same rules of other formulas: if I move a cell appearing in a formula to another place, the formula automatically updates with new position.

My question is: how can I make the formula in cond. formatting update after I move the cell which appear in that formula?
Could it be due to some settings I need to enable/disable?
My example is simplified but I have a lot of formulas I need to cut-paste and I would like all conditional formattings to update.

Thank you in advance for your help!

[Villeroy]

Relative cell references don't shift when you move the cell as you can see when you move a cell with a reference such as =A1. They shift when you copy. Moving means "same thing somewhere else". Copying means "expand this".

[Acidello]

Thanks for the quick reply!
I am sorry my question wasn't clear. I don't want to move the cell with conditional formatting: I want to move the cell from which the formatting depends, so in that case would be as you said: same thing, somewhere else.
If the formatting initially depends on A1, then after moving A1 to C32 the formatting should now depend on cell C32. After moving A1 to C32 the formula in conditional formatting should transform from "A1>0" to "C32>0".

[Villeroy]

Yes, you are right and IMHO this is a bug. The bug has been fixed in LibreOffice but it is still in OpenOffice.

[Acidello]

That's reassuring in a way, thanks! What is the best way to report it? =)

[MrProgrammer]

I have searched for this issue but I found only similar ones, not exactly the same.

Two topics on the forum are: [Issue] Insert column bug and [Solved] Conditional formatting
This is issue 3991 Moving cells does not change cond.formatting references, from 2002, with status CLOSED WONT_FIX.
The developer says:
Relative cell references in conditional formats are never updated when moving cells, because the conditional format for all cells with the same conditional format is treated as one object with one formula. It has to be that way to avoid creating lots of formulas when a large empty cell range has conditional formatting applied to it.

Let me try and explain what "avoid creating lots of formulas" means. I studied this issue (I'm not a developer) in detail a couple of years ago, trying to better understand it. Conditional formatting applies certain tests to a range. I select A1:B1000 with A1 as the active cell. The range is A1:B1000. Format → Conditional Formatting → Condition 1 → Formula Is → $B1 → Cell Style → Yellow → OK. Column B has TRUE/FALSE values. This highlights in yellow pairs of cells in A and B when B contains TRUE. The single CF test, applies to all 2000 cells of one range. When the active cell is A1, $B1 means the cell in "this row, column B". That conditional format is applied to every cell in the range.

Now I cut/paste (move) cell B5 to C5. What is supposed to happen with the conditional formatting that's been applied to A5? I can think of two possibilities.
• A5's CF test is changed to use C5.
• A5's CF test continues to use B5.

But in the first case, the single test which applied to all 2000 cells is now broken. Calc can create a new CF test for A5 but does it somehow modify the 2000-cell test to say it applies to all the cells except A5 and B5 (the two cells which tested B5)? No, CF tests apply to a range, not to a range with some exceptions. Calc could split the CF test into four ranges:
• CF test "this row, column B" for range A1:B4
• CF test "this row, column C" for range A5
• CF test "this row, column C" for range C5
• CF test "this row, column B" for A6:B1000
but the developers realized that this was going to be difficult to program, would create "lots of formulas", and then they decided that A5's CF test would continue to check B5 so only a single range was needed. The closure code for the issue says they do not intend to change this behavior.

My question is: how can I make the formula in cond. formatting update after I move the cell which appear in that formula?

When a cell is moved, Calc will change CF tests which used it in one case: the reference is an absolute reference. This means that the cell reference is of the form $sheet.$column$row. The formula must have three $. Cell references $column$row are not absolute references because they have a relative reference to the current sheet.

The bug has been fixed in LibreOffice but it is still in OpenOffice.

I don't consider the behavior in OpenOffice to be a bug. I believe this is "working as designed" even if some do not like the design. However it seems as if the developers at LibreOffice have decided to change (improve?) the design and program the split of the CF test into multiple ranges, and this happens automatically, the user isn't aware of it. But this automatic split now conflicts with what the user remembers doing: applying conditional formatting to the range A1:B1000.

What is the best way to report it?

This issue was reported long ago and closed. Reporting it again is a waste of your time.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Acidello]

What can I say, thank you so much for your answer and all the links!
"Fixating" the cell in formula with three "$" works perfectly!
Thank you again for your help, have a nice week-end!
Post marked as [Solved] =)

[eeigor]

Excel seems to be breaking the range. There is a known issue called "Conditional Formatting Nightmare (Hell)".