The following 3 bugs occur when copying conditional formats. They are probably closely related or even share the same cause, so I put them into one combined bug report.
a) The behavior is deterministic and fully reproducible.
b) This bug might be related to bug bug 58921, although the observation is somewhat different.
c) The following scenario deals with conditional formatting that is applied to single cells, not to cell ranges.
Steps to reproduce:
1.) Create a new spreadsheet in Calc
2.) Create a new format style to use for conditional formatting
(Here: Style "Red", derived from "Default", with font color set to "Light red")
3.) Add a conditional formatting rule to a single cell (e. g. A10):
Example: If cell value is equal to 5, apply style "Red"
4.) Copy A10 to A11:A13 so that each cell receives the conditional format
5.) Copy the entire cell range A10:A13 to B10:B13 (via copy/paste)
Expected behavior: All cells in B10:B13 now also have the conditional format.
Observed buggy behavior: At first, all cells in B10:B13 behave as if they correctly received the conditional format. But if you open "Format/Conditional Formatting/Manage...", the conditional format is only listed for cell B10. Consequently, after saving and re-opening the spreadsheet file, all other
cells B11:B13 have lost the conditional format.
5.) Select the entire cell range A10:A13 and transfer its formatting to B10:B13 with the "Format Paintbrush" toolbar button
Expected behavior: Same as with bug #1
Observed buggy behavior: Same as with bug #1
5.) Insert a new line 11 below the existing line 10
Expected behavior: The newly inserted cell A11 receives any and all formatting from A10, including the conditional format.
Observed behavior: At first, cell A11 behaves as if it correctly received the conditional format. But if you open "Format/Conditional Formatting/Manage...", the conditional format is only listed for A10, A12, A13 and A14. Consequently, after saving and re-opening the spreadsheet file, A11 has lost the conditional format.
Seems I better shouldn't have used strings like "bug #1..3" as these are automatically converted into links to completey unrelated bug reports. Sorry!
Created attachment 75370 [details]
conditional formatting insert row and copy/paste errors
Confirmed in 22.214.171.124 on Win Vista.
The attachment can be used to test some of the issues:
1) Select Row 1
2) Insert Row
Result is that the formula originally in A1 now in A2 refers to the wrong cell
Similar result if a cell is inserted above A1
3) Copy row 1
4) Paste in row 6 or below
Result is that the formula copied from A1 now refers to the wrong cell
Similar result if any number of rows including row 1 are copied and pasted
Copy and paste of rows not including row 1 (eg rows 2-4) copy the formats correctly
In most cases, the incorrectly referenced cell is 2n-1 where n is the new row number
Occasionally it’s the row range in the formula which is incorrect
Test whether or not the formula in column A is working by altering the text in the adjacent column B
I wonder if the decision to use UpdateReference was actually a good idea. There seems to be too many corner cases that we need to respect to make it fully useful.
I have to think about all corner cases and list them and classify them depending on if they make sense with UpdateReference or not.
Compared to normal formula cells the number of corner cases for conditional format updates is significantly higher.
*** Bug 61890 has been marked as a duplicate of this bug. ***
I wonder if all that is the same bug.
Here are the results of my tests (platform: Vista-32b):
1/ Testing the procedure described in the initial report (comment 0):
1a/ Using Version 126.96.36.199 (Build ID: 5b93205)
-> I confirm all 3 defective behaviours
1b/ Using Version 188.8.131.52 (Build ID: 4102822e3d61eb989ddd325abf1ac077904985)
-> Only the third behaviour is still defective
2/ Testing the procedure described in comment 2 with its associated file:
2a/ Using Version 184.108.40.206 (Build ID: 5b93205)
-> I do not reproduce
2b/ Using Version 220.127.116.11 (Build ID: 4102822e3d61eb989ddd325abf1ac077904985)
-> I reproduce only the defective insertion of row above row 1
*** Bug 58921 has been marked as a duplicate of this bug. ***
I also noticed that copying conditional formatting between different tables of the same document, or between different documents, may cause all sorts of problems with the formatting of the copied cells (also in the current LO 4.1.2 release).
As with the other observations, the problems sometimes don't show up immediately after copying, but only after saving and re-opening the document. It seems that checking the list entries under "Format/Conditional Formatting/Manage..." is a good indicator of whether something went wrong with the conditional formatting.
(In general, I start to wonder if it was a good design decision to store conditional cell formatting separate from the "normal" (static) format. If all format settings of a certain cell were stored together in one place, it would probably be much more robust and consistent when being copied.)
(In reply to comment #7)
> I also noticed that copying conditional formatting between different tables
> of the same document, or between different documents, may cause all sorts of
> problems with the formatting of the copied cells (also in the current LO
> 4.1.2 release).
Unrelated to whatever original problems were reported in this bug, the copying between sheets problem is fixed with bug 61946.
*** Bug 68776 has been marked as a duplicate of this bug. ***
I confirm the problem. It started happening with LO 4.0 , also confirmed in LO 18.104.22.168 Linux. There is always an error when pasting-special formatting only, and there is an error when pasting cell with conditional formatting. What happens is that the formatting is pasted to a custom cell but not the one pasted. I found many duplicates of this bug. It practically means no conditional formatting until fixed. This thing is really annoying, I consider it critical.
I previously managed to reproduce bug 39484 which led to resolution. I am willing again to try hard to deal with this one. I am kindly asking for directions on what to do.
(This is an automated message.)
Setting priority to highest as this is a 4.1 MAB. This is part of an effort to make the importance of MAB reflected in priority too.
Saving a spreadsheet as .xls seems to preserve more — though not all — conditional formatting than other file formats.
please retest against 22.214.171.124
if bug persists please move it to mab4.2 list since 4.1.x is EOL
It's not working the same as before but something weird is still going on. If you insert a row above R1 the resulting line works as if the conditional formatting was there (as it should) but with Format > Conditional Formatting > Manage there are no references to the new R1. If you insert a line above a lower line, there are references to the new line. The software must be generating A1:A2 conditional formatting internally even though it's not being shown.
I had problems this week with conditional formatting with only one condition. It didn't transfer correctly from LO to MSO and in the end I had to do the work (for someone else) in MSO. When working there, I noticed that MSO refused to save a spreadsheet with conditional formatting in .xls format, but it works fine with .xlsx
I need to do more to extend all of the above, but I have no time until later. I plan to do it in conjunction with the bug chase on 4.3.0 next week.
In the meanwhile, please would someone move this to MAB 4.2?
Also, it would be good if someone would respond to comment 10 and give kougasio some hints (added as a cc).
*** Bug 76522 has been marked as a duplicate of this bug. ***
This bug report is a total mess. If there is still an issue please open new bug reports with one bug per report.
> This bug report is a total mess.
I just checked my original report and it still is clear and precise. In addition, according to the comments and duplicate bug reports, it still applies to the latest versions. The only inappropriate thing is that someone set it to "RESOLVED NOTABUG" while it is neither resolved nor "not a bug".
> If there is still an issue please open new bug reports with one bug per report.
Sure, one report per bug is reasonable. Would you still recommend doing so even although the observed misbehavior is likely to be related to a common cause?
Please report new bug reports as requested by Markus (who is one of our expert developers).
(In reply to comment #18)
> Please report new bug reports as requested by Markus (who is one of our
> expert developers).
Ok, I'll do so as I come across issues that I can reliably reproduce with the current version. A first spin-off is bug 81611.
BTW: Keep in mid that after this bug has been closed, those bugs that were previously closed as duplicates should be revisited and possibly reopened if they still apply to the current version (such as bug 61890, bug 68776, bug 76522).
*** Bug 65660 has been marked as a duplicate of this bug. ***