Apache OpenOffice (AOO) Bugzilla – Issue 4155
References in conditional formating don't always change correctly
Last modified: 2017-05-20 10:45:09 UTC
Set up a speadsheet with some numbers in column e.g. C. Some other numbers in Column L will be formatted red and green depending on L<C (red) and L>C (green). Now insert a new column in after column H. The formatting conditions have changed to M<D (red) and M>D (green) when M<C (red) and M>C (green) would be correct.
Hi Michael, I'm sorry to tell you that this Issue is not reproducible with an OOo641d, therefore I have to close it. Best regards Frank
closed because works for me. Frank
Now I've set the Radio button in the correct way. It's closed
Got an bugdoc from Michael and it's reproducible. Frank
Hi Niklas, if you use relative addressing both used cells are changed if you use absolute addressing only the cell address after the inserted column is changed. This must be done also for relative addressing. Frank
Relative references in conditional formats work like relative references in named ranges: They aren't updated when columns/rows are inserted/deleted, because they can be used in different cells, so there is no fixed reference to compare to the inserted/deleted range.
As mentioned on the qa dev list on March 5th I will close all resolved duplicate issues. Please see this posting for details. First step in IssueZilla is unfortunately to set them to verified.
As mentioned on the qa dev list on March 5th I will close all resolved <wontfix/duplicate/worksforme/invalid> issues. Please see this posting for details.
*** Issue 39772 has been marked as a duplicate of this issue. ***
*** Issue 66441 has been marked as a duplicate of this issue. ***
*** Issue 66885 has been marked as a duplicate of this issue. ***
Hi. In my opinion, either this issue or issue 66441 should be reopened, because there is INCONSISTENT behavior, not just undesired behavior. OOo sometimes updates cell references in conditional formatting properly, and sometimes does not, depending on the situation. If references are not to be updated, then they should NEVER be updated instead of being updated in certain situations and not others. This is aside from the observation that a large number of users have viewed the lack of updating as a bug and reported it as such, making WONTFIX a less than desireable solution. If you like, I can open a second issue about the inconsistent behavior, or I can repoen this issue. If I don't receive any comments I will reopen this issue rather than create a new issue.
It's consistent with named ranges, after which it was modeled. We can reopen this as an enhancement issue, but don't expect it to be changed quickly.
Reopened.
Changed issue type to enhancement. Suggested Enhancement: Make conditional formatting update when cells/rows/columns are deleted or inserted and relative referencing is used. Note that when absolute referencing is used, the cell references already update properly (see issue 66441). Note also that absolute referencing for the cell coordinates but relative referencing for the SHEET name only in the cell reference will break updating. Please see issue 66441 for a more complete description of the problem. Added ms_interoperability keyword, as this issue can break imported functionality of Excel spreadsheets if the user is not careful.
target
I can confirm the issue with absolute references, where no sheet name is used (for instance, using $C$1 instead of $Sheet1.$C$1). See also my comment for issue 71104.
*** Issue 71104 has been marked as a duplicate of this issue. ***
*** Issue 76603 has been marked as a duplicate of this issue. ***
Hi! Is there any workaround meanwhile? It is very annoying if one has a lot of conditional formatting in a document. Using absolute sheet references is not an option in my case.
Hi! Please do anything about this issue. This is really annoying, and in my opinion a bug, and not an enhancement. Moving cells is handled correctly for normal formulas, and conditional formatting should behave exactly the same. There is obviously no way around this issue except manually fixing every single conditional formatting formula--and not even find and replace is possible. Best regards
*** Issue 84459 has been marked as a duplicate of this issue. ***
Hi! Any chance that this will be fixed soon?
*** Issue 92628 has been marked as a duplicate of this issue. ***
Modify OS due to Issue 92628
Hi! Could this be changed back to issue type "defect", please? I guess otherwise this will never be fixed. There have already been 7 duplicates of this issue between 2002 and 2008.
Could you set a target milestone for a fix for this issue, please?
I would also like to know if this is ever going to be corrected. When?
This needs more emphasis than merely an "ENHANCEMENT".
Any chance to get this fixed for 3.1? It is no enhancement. It is a real bug.
Could you at least define a specific target milestone? Tis BUG has been known over 7 years now, and it is a basic one (happens with native open office documents, not only with Excel documents). This is really annoying.
I've been waiting for more than a year now and the bug was filed 2002! This bug is classified as an enhancement due to that it behaves according to the modelling. The Conditional formating FEATURE is buggy and that the reason is an incorrect modelling still makes it a bug, not an enhancement. An example of an enhancement is to extend then number of conditions per cell from 3 to 7, nowdays supported by Excel. I propose that this is introduced at the same time but the most urgent request is to fix the bug itself. Myself I filed a bug #235602 in Launchpad a year ago https://bugs.launchpad.net/ubuntu/+source/openoffice.org/+bug/235602 The least I can expect is that this bug is changed from enhancement to be treated as what it is, i.e. a bug. Moreover I expect a milestone to be set. I'm waiting and stuck in Excel due to this annoying bug.
This should be considered a bug. I make extensive use of conditional formatting, and doing so in Open Office stops any further activity on the spreadsheet because any row / coloumn changes (add / delete) immediately breaks all conditional formatting, and is not acceptable. If you use relative references in your conditional formatting, you can't add/ delete a row/coloumn that moves your formatted cell. If you use absolute references, you cannot add/delete a row/coloumn that will move your referenced cell. This is the single most important reason I can't use OpenOffice, and I can't recommend it in this condition. It's that serious of a bug. I use conditional formatting to quickly visualise the state of the data and it helps me prevent any editing errors, for example. But I cannot do it using OpenOffice, because once I design the conditional formatting functions, the document is pretty much fixed.
It is incredible a basic bug like that can be ignored for so many years. It simply prevents me from using openoffice. Conditional Formatting is absolutely key for spreadsheets used as dashboards and cockpits. Visual dashboards is one of the main reasons for people to buy Excel. And for sure people that build dashboards add rows periodically. The worst part is the problem involves a formula that is embedded in a dialog box and therefore the old nice "find-replace" does not work here. I cannot see any way to work with it, except if I get back to Excel, insert a hundred of rows, and then send it back to openoffice. But if I do so, why should I come back to openoffice? I believe this kind of question is exactly the kind of question MS loves.
Yes, incredible. 7.5 years. I do not believe this will be fixed ever. Do not get me wrong, I have bought several versions of StarOffice, but on the other hand, 80,- for MS Home and Student is not so much either.
Unbelievable! If OO.o didn't plan to fix it since 7.5 years ago, please just close this issue, or please do a warning message to all users who TRUST OO.o. I think the latter is much easier to be carried out.
Any of you are more than welcome to submit a patch to fix this. After 7.5 years no one has submitted a patch & people still expect others to fix it for them... To me that's also unbelievable.
Maybe this is a problem of opensource software. You can never expect anything. On the other hand, this software is sold as StarOffice, and considering that Excel has handled conditional formatting correctly forever, as paying customer you would expect that such rather basic functionality will be fixed sometime in StarOffice as well (without doing it yourself).
I realize the behavior of this function is not working as people expect. This feature should probably be marked as "incomplete" (if possible), and the OpenOffice documentation updated to explain that the desired behavior of absolute addressing with reference to conditionally formatted cells during insert/delete/move/copy events is broken, and may never get fixed. This way, people like me who spent too much time trying to make it work in a practical application have a chance to learn it doesn't work when we read that documentation (which is the first place people like me look for help). I wish I could program, and fix this behavior for everyone. Furthermore, I wish I had unlimited amount of money, and could afford to pay someone to delve into the code and fix this behavior. I wonder if this would be squashed any faster if a case of beer was offered to the lone coder that fixes this.
This is a bug. I, too, can't do the programming but wish I could. There could be a basic fault in the algorithms for conditional formatting which needs to be addressed. It is similar to bug 123667 (transfer from .xls files) which makes .xls files incompatible with Ooo with respect to conditional format If wo cells are linked by formula, and one moves with respect to another, we correctly update the formula. Can the same methods be used to update conditional formulae?
Reset the assignee to the default "issues@openoffice.apache.org".