Download it now!
Bug 59288 - FORMATTING: Copying conditional formatting
Summary: FORMATTING: Copying conditional formatting
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.4.3 release
Hardware: x86 (IA32) Windows (All)
: highest normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 58921 61890 65660 68776 76522 (view as bug list)
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2013-01-12 17:44 UTC by Lenge
Modified: 2014-07-25 14:20 UTC (History)
14 users (show)

See Also:
Crash report or crash signature:


Attachments
conditional formatting insert row and copy/paste errors (11.16 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-02-22 20:55 UTC, mike.hall
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Lenge 2013-01-12 17:44:38 UTC
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.

Remarks:
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

Bug #1:
-------
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.

Bug #2:
-------
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

Bug #3:
-------
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.
Comment 1 Lenge 2013-01-12 17:49:29 UTC
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!
Comment 2 mike.hall 2013-02-22 20:55:38 UTC
Created attachment 75370 [details]
conditional formatting insert row and copy/paste errors

Confirmed in 4.0.0.3 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
Comment 3 Markus Mohrhard 2013-02-23 03:07:44 UTC
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.
Comment 4 Jorendc 2013-03-06 11:34:58 UTC
*** Bug 61890 has been marked as a duplicate of this bug. ***
Comment 5 Michel Rudelle 2013-03-10 16:50:17 UTC
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 3.6.5.2 (Build ID: 5b93205)
     -> I confirm all 3 defective behaviours
1b/  Using Version 4.0.1.2 (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 3.6.5.2 (Build ID: 5b93205)
     -> I do not reproduce
2b/  Using Version 4.0.1.2 (Build ID: 4102822e3d61eb989ddd325abf1ac077904985)
     -> I reproduce only the defective insertion of row above row 1
Comment 6 Rainer Bielefeld Retired 2013-04-09 17:53:26 UTC
*** Bug 58921 has been marked as a duplicate of this bug. ***
Comment 7 Lenge 2013-10-15 07:26:21 UTC
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.)
Comment 8 Eike Rathke 2013-11-23 12:26:41 UTC
(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.
Comment 9 Owen Genat (retired) 2013-12-14 23:59:32 UTC
*** Bug 68776 has been marked as a duplicate of this bug. ***
Comment 10 koukasio 2013-12-15 00:44:51 UTC
I confirm the problem. It started happening with LO 4.0 , also confirmed in LO 4.1.3.2 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.
Comment 11 Björn Michaelsen 2014-01-17 09:51:54 UTC
(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.
Comment 12 Hugh Hyatt 2014-02-03 18:41:38 UTC
Saving a spreadsheet as .xls seems to preserve more — though not all — conditional formatting than other file formats.
Comment 13 tommy27 2014-05-13 05:28:07 UTC
please retest against 4.2.4.2
if bug persists please move it to mab4.2 list since 4.1.x is EOL
Comment 14 mike.hall 2014-05-13 08:43:01 UTC
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).
Comment 15 Joel Madero 2014-07-16 20:26:30 UTC
*** Bug 76522 has been marked as a duplicate of this bug. ***
Comment 16 Markus Mohrhard 2014-07-19 12:20:11 UTC
This bug report is a total mess. If there is still an issue please open new bug reports with one bug per report.
Comment 17 Lenge 2014-07-20 15:56:08 UTC
> 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?
Comment 18 Joel Madero 2014-07-20 17:40:13 UTC
Please report new bug reports as requested by Markus (who is one of our expert developers).
Comment 19 Lenge 2014-07-21 15:37:19 UTC
(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).
Comment 20 ign_christian 2014-07-25 14:20:22 UTC
*** Bug 65660 has been marked as a duplicate of this bug. ***