Apache OpenOffice (AOO) Bugzilla – Issue 71104
Conditional formatting lost if reference used in condition is not absolute
Last modified: 2013-08-07 15:12:27 UTC
Hi, This defect has been detected on Linux and reproduced on Windows, both in French and English version. When using conditional formatting on a cell, if the condition refers another cell using a modifiable reference (like A$1), the formatting is lost if cell A1 becomes A2 by inserting a new row. Steps to reproduce : - Create a new Spreadsheet - In cell A1 insert 1 - In cell A2 insert 1 - Select cell A2 and apply conditional formatting via Format > Conditional Formatting ... - In Condition1 select "Cell Value is" / "equal to" / $Sheet1.A$1 / Cell Style : Heading Note: Pointing to A1 will insert $Sheet1.$A$1, modify it to $Sheet1.A$1 (useful if you use copy/paste special/format) - The style of cell A2 changes to Heading. - Select Row 1 - Insert a new row via Insert > Rows (A1 becomes A2, A2 becomes A3) - The style of cell A2 changes back to normal - Check the conditional formatting, it still references $Sheet1.A$1 Expected Result : - The conditional formatting should reference $Sheet1.A$2 This issue will not appear if the conditional formatting references A1 using an non modifiable reference ($Sheet1.$A$1). I can attach a spreadsheet if necessary but I think the steps to reproduce are easy enough to see the issue. Thanks.
This is confirmed with OOo version 2.1 milestone build 680_m197 on Windows XP
Confirmed in Oo 2.1 German under Windows XP, too.
PS: Please notify me of updates.
I can confirm the issue, and this also happens with absolute references (at least with OO 2.1): - Open a new spreadsheet - Enter the following formula to cell A1: =$C$1 and the following value to cell C1: aa1 - Add a single condition for conditional formatting to cell A1 and A2: Cell value is equal to $C$1; Cell style "Heading" A1, respectively the string "aa1", is formatted with the Heading style. - Right-click the header of column B - Select Insert Column A1, respectively the string "aa1", has lost its Heading format!!! The condition is still Cell value is equal to $C$1; Cell style "Heading" which is obviously not correct. This is also not consistent with cell editing. "aa1" moves from C1 to D1, and A1 is adapted correctly to "=$D$1". So, for formulas it works, but for conditional formating, it does not.
Addition to my previous post: The problem does not occur with absolute references including absolute sheet reference, for instance $Sheet1.$C$1.
Hi, not a bug but exactly what you've told Calc to do. You've made the colum relative and hold the line absolute. So this is exactly how it should work. Frank
closed invalid
Created attachment 42117 [details] Conditional formatting example
Sorry, but I am not sure we mean the same. I DID NOT make the column relative. I just mentioned that the sheet reference is relative, since I omitted this. I have created a sample doc and attached it. It is exactly my previous example. And it is a defect. Just open the document, select the header of column B, and then insert a column (between A and C). You will see, that A has still the value aa1 as before, although it is a formula (previously pointing to C1, and now pointing to D1), but the format changed from Heading to Normal, since the conditional formating still points to C1. This is not consistent and it is annoying to adapt conditional formatting formulas everytime you insert a column between the referring and the referenced column. If you insert a new colum before A or after C, there is no problem. Best regards
fst, please reopen the bug, or make another one if you think my posts do note relate to the original post. From user's point of view these are the same issue.
I agree with shaber. Maybe my first explanation wasn't clear enough but the behavior is not consistent between conditional formatting and the value of the cell. If you reference cell $C$1 in value and in conditional formatting, you expect them to be modified in the same way whenever a column is inserted between columns A and C. In this case, the value of cell A1 becomes $D$1 but the conditional formatting is still $C$1. By the way, Excel keeps conditional formatting in sync with cell references, I don't say it's a reference, but in this case I find the behavior more consistent. Thanks.
After discussion with development this is targeted to OOo Later for further evaluation. Frank
Actually this is a dupe. *** This issue has been marked as a duplicate of 4155 ***
Closing dup.