Apache OpenOffice (AOO) Bugzilla – Issue 3991
moving cells does not change cond.formatting references
Last modified: 2010-12-15 13:14:01 UTC
When you have a conditional formatting formula referencing anothe cell and you _move_ that cell, references in the formula are not moved. EG.: 1.- we have a cell A1 with a format condition formula saying : "when A2 is someting". 2.- Then we move cell A2 to another location (by either cut&paste or inserting rows/columns before it). 3.- The formula on the format condition of A1 keeps pointing to A2 instead of pointing to the new location. greetings Kiko
I'll have a look, Peter
Hi Kiko, works fine for me. Could you please attach the document where you found it. Best regards, Peter
Created attachment 1365 [details] test spreadsheet
Hi Peter, Here it is the document: 1.- The format condition in cell A1 is "B1=C1". 2.- If I move cell B1 to B2 with a "cut & paste" 3.- Then the condition remains "B1=C1", and I understand it should have changed to "B2=C1" (as it would do if it was a formula in the cell, not in the format conditions). Regards Kiko
Hi Niklas, this behaviour is reproduceble when the conditiolnal formatting has a syntax like formula is... B1=C1. $B$1=$C$1 works as expected. Best regards, Peter
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.
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. 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 57665 has been marked as a duplicate of this issue. ***
I've been punted here from issue 57665; where I think I have a clearer example of the fault. I'm quite surprised that this issue is closed. The problem exists and the answer seems to be "well it's hard to fix". NN's comment above doesn't seem to address the problem to me. Saying "the conditional format for all cells with the same conditional format is treated as one object with one formula" just sounds wrong - the conditional format can be copied to multiple cells and updated as it goes, it can be edited by hand to show the correct thing - why can't it be altered automatically when a column is inserted?
This issue gets even worse if you use labels in the conditional formatting formula. If you insert a column within a label row, the labels used in the formula changes name to the next one in the row. I would be pleased if labels are kept, while relative cell references are the way they are, but I'd prefer both. I use conditional formatting alot, and when I add columns, I have to redo it manually. In my opinion, formulas in the conditional formatting entries should move just like the content of the cells. I can not see the big programming challenge in expanding or clipping objects of equal condition when you do row/column del or ins, or even copy and paste. Since this is a very old issue, maybe its time to reconsider it?