Issue 4155 - References in conditional formating don't always change correctly
Summary: References in conditional formating don't always change correctly
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: 641
Hardware: PC All
: P3 Trivial with 23 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: ms_interoperability, oooqa
: 39772 66441 66885 71104 76603 84459 92628 (view as issue list)
Depends on:
Reported: 2002-04-21 12:27 UTC by mgd
Modified: 2017-05-20 10:45 UTC (History)
7 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description mgd 2002-04-21 12:27:33 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)
M<C (red) and M>C (green) would be correct.
Comment 1 frank 2002-04-22 13:44:20 UTC
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 

Comment 2 frank 2002-04-22 13:45:00 UTC
closed because works for me.

Comment 3 frank 2002-04-22 13:46:32 UTC
Now I've set the Radio button in the correct way. It's closed
Comment 4 frank 2002-04-23 09:44:30 UTC
Got an bugdoc from Michael and it's reproducible.

Comment 5 frank 2002-04-23 09:49:42 UTC
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.

Comment 6 niklas.nebel 2003-01-15 17:01:20 UTC
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.
Comment 7 michael.bemmer 2003-03-11 18:08:21 UTC
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.
Comment 8 michael.bemmer 2003-03-11 18:16:15 UTC
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. 
Comment 9 frank 2005-01-17 10:09:27 UTC
*** Issue 39772 has been marked as a duplicate of this issue. ***
Comment 10 frank 2006-06-30 09:43:21 UTC
*** Issue 66441 has been marked as a duplicate of this issue. ***
Comment 11 frank 2006-06-30 10:40:52 UTC
*** Issue 66885 has been marked as a duplicate of this issue. ***
Comment 12 atdsm 2006-06-30 13:30:17 UTC
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

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.
Comment 13 niklas.nebel 2006-06-30 15:16:13 UTC
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.
Comment 14 atdsm 2006-06-30 15:30:40 UTC
Comment 15 atdsm 2006-06-30 15:36:10 UTC
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.
Comment 16 niklas.nebel 2006-07-07 19:37:06 UTC
Comment 17 schaber 2007-01-02 17:21:19 UTC
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.
Comment 18 ooo 2007-01-23 14:54:18 UTC
*** Issue 71104 has been marked as a duplicate of this issue. ***
Comment 19 frank 2007-04-25 10:32:27 UTC
*** Issue 76603 has been marked as a duplicate of this issue. ***
Comment 20 schaber 2007-05-28 20:30:40 UTC

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.
Comment 21 schaber 2007-06-27 07:33:30 UTC

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
Comment 22 frank 2007-12-12 09:42:44 UTC
*** Issue 84459 has been marked as a duplicate of this issue. ***
Comment 23 schaber 2008-03-07 22:50:27 UTC

Any chance that this will be fixed soon?
Comment 24 frank 2008-08-10 20:51:45 UTC
*** Issue 92628 has been marked as a duplicate of this issue. ***
Comment 25 Rainer Bielefeld 2008-08-11 05:12:49 UTC
Modify OS due to Issue 92628
Comment 26 schaber 2008-09-30 20:15:43 UTC

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.
Comment 27 schaber 2008-12-05 15:40:57 UTC
Could you set a target milestone for a fix for this issue, please?
Comment 28 renejr 2008-12-11 21:12:37 UTC
I would also like to know if this is ever going to be corrected.  When?
Comment 29 renejr 2008-12-11 21:15:21 UTC
This needs more emphasis than merely an "ENHANCEMENT".
Comment 30 schaber 2009-02-02 11:29:31 UTC
Any chance to get this fixed for 3.1?

It is no enhancement. It is a real bug.
Comment 31 schaber 2009-05-02 18:57:59 UTC
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.
Comment 32 bowmores 2009-07-13 13:25:42 UTC
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

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.

Comment 33 hesido 2009-08-20 19:37:05 UTC
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 

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 

Comment 34 wilsonc 2009-10-19 03:02:18 UTC
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.
Comment 35 schaber 2009-11-30 01:16:29 UTC
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.
Comment 36 toresvn 2010-03-11 12:02:47 UTC
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.
Comment 37 kyoshida 2010-03-11 13:57:23 UTC
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.
Comment 38 schaber 2010-05-16 14:17:13 UTC
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).
Comment 39 tristan_young 2010-06-29 01:30:30 UTC
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.
Comment 40 wrigh 2013-11-14 17:23:29 UTC
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?
Comment 41 Marcus 2017-05-20 10:45:09 UTC
Reset the assignee to the default "".