Description: In an auto-sorted array, where a column has a conditional format of 3 colours, orphan and non-contiguous array references appear in the format manager when additional rows are inserted into the array. The formatting is correct but the manager will show an orphan entry for the inserted cell(s) and the portions of the array which precede and follow the insert point will have two references excluding the orphan. Notably, the second portion will appear first followed by the first portion. Fortunately, the manager will permit the removal of the orphan and overwriting the non-contiguous references with a single contiguous reference. Many inserts will result in numerous orphans and "snippets" which do make a "joined up" chain. Steps to Reproduce: 1. Create a column with drag-filled dates and then make that an auto-filtered column 2. Conditionally format the array with three colours - it's fine to accept the defaults 3. Insert a row about midway and copy paste a cell from another location. It makes sense to copy a date firly well removed from the insert point 4. Bibisect the array a couple of times and repeat step 3 5. Observe that the FORMAT>CONDITIONAL>MANAGE facility will now list all the inserted cells as orphans together with a number of small ranges matching the number of inserts 6. Delete the orphans and edit one contiguous range 7. It doesn't crash anymore but it would be nice if you could fix it for me - Yesterday ;) Actual Results: The impact upon the colour rendition functions normally but following any number of inserts the process of assessing the range and implementing the colour rendition must take magnituides of processor cycles to assess "what is the range requiring this conditional format" Expected Results: One contiguous list in the Conditional Format Manager Reproducible: Always User Profile Reset: No Additional Info: Version: 7.3.6.2 (x64) / LibreOffice Community Build ID: c28ca90fd6e1a19e189fc16c05f8f8924961e12e CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded And 7.4.2.3 on one of my other boxes which was specifically installed to test a related bug on the latest release.
Hi Colin, thanks for reporting. Can you see if this is the same issue as in bug 151638?
(In reply to Rafael Lima from comment #1) > Hi Colin, thanks for reporting. > > Can you see if this is the same issue as in bug 151638? Hi Rafael, It does look remarkably similar although the actions creating the anomaly are different. In my case, I only inserted cells into the middle of the column but the other report has the cells moved out of the column. My report is probably the simplest for applying the logic that if I extend the single plane then I would anticipate the list just growing to accommodate the enlargement. I would imagine the impact of moving the cells out of the alignment plane could present two alternatives which should be defined by the user or defined as a known precedent:- 1. Should the format follow the cell and be included in the colour rendition? 2. Should the chain of the conditional format be broken if a cell is moved out of alignment? Then at least two questions arise:- 1. What to do with the newly "emptied" cell format a) keep the CF b) duplicate the CF for a new non-contiguous range 2. The whole concept of follow me when dragging, copying, pasting etc. I'm not sure if it helps you to know that my report is just the "rump" of another bug 143049 where the insertion of cells into a defined table alongside another defined table was causing a system crash. I don't envy your task.
The reason to this problem is that when you copy a cell from outside the Conditionally Formatted column and paste it in the newly created row (inside the conditionally formatted column), it brings its own conditional format information, hence overwriting the original conditional format. You can bypass this issue if you use Paste Special and paste only the values (not the formats), then the conditional format won't be overwritten. I believe the current behavior tries to mimic what MS Excel does. In MS Excel you'll get the exact same result as you get in LO Calc. And to overcome this in Excel you have to use Paste Special as well. Let's hear the opinion from others, but this might be NAB.
If The range is created with the dates and CFed with the colour scale and a cell is then inserted into the middle - it extends the range of the CF which is what I would expect. If I copy the last cell into the empty location with paste special it does indeed conform to the three colour scale. However, if I insert yet another cell directly above the first insertion (the darkest green one) and copy the last cell into that location the new cell is not CFed - The colour scale has been annulled but this annulment is not reflected in the CF manager. The CF manager now indicates that the singleton cell thus created has its own scale format. The characteristics of this orphan format are identified as identical to the surrounding range - from which the source was copied - but in reality, no formatting is effected. Surely this can't be right. The copied cell brought its own format which is the range format in which it resides but the new cell is unformatted despite every indication that it has been formatted. The orphan together with the two non-contiguous ranges ineffectively overlap. I think, if Excel is identical, then they both have an "interesting" interpretation of this particular situation. It's probably fair to say that moving things into and out of the defined CF range adheres to the existing logic and mechanics of such an action and the consequences of that action need to be better understood by users who may intuitively believe the change wil aLways be what they are expecting rather than the logical consequences of complex "follow me" processing.
(In reply to Rafael Lima from comment #3) > .... and paste it in the newly created row (inside > the conditionally formatted column) Not sure if it's just semantics or more significant but you refer to a newly inserted row and my definition is to insert only a cell into a single column
(In reply to Rafael Lima from comment #3) > Let's hear the opinion from others... I'm having a hard time to follow the issue. Is there a simplified example with less but very clear steps to reproduce?
(In reply to Heiko Tietze from comment #6) > (In reply to Rafael Lima from comment #3) > > Let's hear the opinion from others... > > I'm having a hard time to follow the issue. Is there a simplified example > with less but very clear steps to reproduce? Perhaps steps 1 - 5 in the first report? Attached Zip demonstrates the holes in the range produced by simply inserting cells and copy-pasting or drag filling The copy-paste produces an ineffective colour scale for D10 and D15 - both of which claim to be three colour CF. The drag fill was D13 dragged into D14 where two cells had originally been inserted The cut and paste was D19 to D10 and D15
Created attachment 183492 [details] Zip with file and image
(In reply to Colin from comment #8) > Created attachment 183492 [details] > Zip with file and image I forgot to turn the array into an autofilered array but I'm convinced you won't need help with that.
Created attachment 183497 [details] Example screenshot I think the autofilter makes no difference. My STR: * Enter data into A1:A10 like 1.1.2022, 1.2.2022 etc. * Enable CF / Color Scale -> makes it green to red * Cut one item and move everything into the free space -> destroys the range and makes the CF non-contiguous The expectation is to keep the CF range as it is. Everything correct?
(In reply to Heiko Tietze from comment #10) > Created attachment 183497 [details] > Example screenshot > > > The expectation is to keep the CF range as it is. Everything correct? Hopefully you noticed that row 13, whilst claiming to be CF 3 colour from green to red now renders as white. It's not just a case of keeping one contiguous range but also ensuring that all cells in that range are CFd. Fingers crossed that something else doesn't pop up out of the woodwork when you remedy the context
[Automated Action] NeedInfo-To-Unconfirmed
Afraid this range handling is a feature and very useful in some scenarios but obviously failing for the CF case. Anyway, let's forward the request to the developers.
*** This bug has been marked as a duplicate of bug 129814 ***