Conditional Formatting does not paste correctly

I have a spreadsheet in which one column has conditional formatting.

I insert some blank rows. I then copy from a row that has formulas but no data, in order to populate the blank rows with formulas before entering data in them.

The column with the conditional formatting contains a formula which evaluates to zero unless there is an error in the data that I entered. This column is formatted to show in red, but the conditional formatting displays this in pale grey if it is zero.

Before I enter data, this evaluates to zero, but in the newly pasted rows it shows red. If I copy a cell from that column (rather than a whole row), and paste it on there, it correctly goes grey. This is true whether I copy from a cell that is displaying correctly or one that is displaying incorrectly. I can even copy a cell onto itself, and it turns from red to grey.

If I save the spreadsheet, close LibreOffice, and open the spreadsheet again, all rows display correctly, but any further rows I insert display incorrectly again.

I copy some data from a bank statement I downloaded, and the column correctly shows an error until I enter data elsewhere in the row. I now find that sometimes the error column remains red when I have entered the correct data and cleared the error. After I sorted the spreadsheet, this now happens in the rows I inserted, in the new locations where they have gone - i.e. they have taken the problem with them. This behaviour is not consistent - usually the error column behaves correctly, going grey when I clear the error.

Where I have copied and pasted cells (or formats) to correct the conditional formatting, it creates new regions on the list of conditional formatting, which could bloat the spreadsheet in the long term

The spreadsheet was created in OpenOffice 4 under Windows 7, where it worked correctly; I have only just migrated to LibreOffice 5.4.7 and Windows 10.

can you show us your file? all data from cell may be deleted

I know about the issue of duplicated conditions, there is a bug reported pretending that this doesn’t happen, tdf#87274, because it have two effects, spreadsheet having a lot of CF, difficult to maintain, while all of them doing the same for different and contiguous ranges, what sometimes can carry to a high degradation of the performance.

The better way I have found it’s using paste special to copy without format, extending the range of the CF manually editing it, or having already it long enough. Going to do a lot of copy/paste perhaps can be quick reducing the range in the CF, restoring it after finish the copy/paste.

The issue about increasing format count on paste (creation of separate formats with new ranges) is bug 95295, which is fixed for version 6.1. The issue that @mariosv mentioned is about joining existing fragmented formats when a file with them is opened.

The problem with wrong display of formatted cells after paste (until the file is reloaded) needs a sample file (as @kompilainenn suggested) with specific steps to reproduce, like “copy range X1:Y2 to clipboard; select V3:W4 and paste; expected is A; actual is B”. Possibly this is a bug - then it will need to be filed to bug tracker; or it might be fixed in 6.0 or later.

Thanks for pointing me at bug tracker. I think this is bug 62208. It appears to be still open. I can reproduce the bug easily in a new spreadsheet, so I shall post instructions there as to how to do that.

Only do that if you can add something substantial to the already existing description there; otherwize it will only be noise in the bug, that only makes the bug less manageable. (However, I see that the bug has a request to confirm that with a current version; please do it if you can.)

Adding yourself to CC of the bug “promotes” it collaterally, since it’s kind of a measure of “affected user count”.

Looking more closely at 62208, I now don’t think it is the same bug. I intend to submit a bug report, but probably not before next week.

At least I cannot reproduce either of bugs mentioned in that bug’s test document, using Version: 6.0.5.1 (x64)
Build ID: 0588a1cb9a40c4a6a029e1d442a2b9767d612751
CPU threads: 4; OS: Windows 10.0; UI render: default;
Locale: ru-RU (ru_RU); Calc: CL

I can still experience this bug 7.0.1.2. “Master” sheet has 6 conditional formating rules (Format->Conditional->Manage show 6 entries). I right click the sheet’s tab, select ‘Move or Copy Sheet…’, add a copy to the end of the document and the resulting new sheet only has 5 conditional formating rules left. I can keep creating copies of copies which ultimately yields completely broken results.