Description: Insertion and deletion of columns with conditional colour scale formatting doesn’t create a consistent colour rendition. As inserted columns are deleted, the colours sometimes appear to reflect the maximum values that were ever seen in the “table” depending upon the order in which the data columns are erased. When the table is reduced to only 1 column – in this instance the original column, the colours don’t seem to reflect the range of numbers remaining. Attached is a simple example spreadsheet. The column deletions are destructive so the second reference table L11;N17 has been created out of the “line of fire” to illustrate the variations. I have experimented with deleting and inserting new cells/columns as opposed to deleting and undoing the action and this produces almost random adjustments to the editable Conditional Formatting table – inspection reveals numerous overlapping schemes which seem to be created by the moving and insertion events but not removed by the deletions. Selecting and copying the “reference” table onto the G1;I7 table after it has been damaged by deletion and re-insertion also doesn’t replicate the colour scheme correctly. Saving and then reloading the file whilst the colour scales are “damaged” then appears to inconsistently restore colour scaling to G1;I7 in a vague representation of reality. Steps to Reproduce: Create a simple “pulldown” sequence of numbers from 1-7 in column G. Select and conditionally format the range with the default 3 colour scheme. Select the column and insert a column after this table’s column – a new column H. Observe that both the cell content format and the conditional formatting is copied to the new “implied” table of seven cells from the left column G. If the new column is inserted before the source column then the formatting is replicated from the column to the left of the insert point – The former column F. xxxxxxx Herein lies one procedural question – should the format replication be created from the selected or “source” reference ie column G being the right column? - For me, it seems more logical to consider the before/after relationship to the source column rather than simply default from the left - as the source column is more likely to be providing the inspiration for the insert.xxxxxxx Having inserted the new column, “pulldown” the values 8-14 and observe the new colour rendition. Now insert a new column between G & H (it matters not which is the “reference” column – the formatting will always be copied from the left or column G). Now “pulldown” the values 15-21 in empty column H and observe the new colour rendition. Select and delete the values in column H and observe the new colour rendition – seems consistent. Undo the deletion and observe the colour rendition. Select and delete the values in column I and observe the unchanged colour rendition - seems inconsistent. Delete Column I observe the colour rendition – what should be consistent? Delete Column H observe the colour rendition - what should be consistent? Undo the two column deletions – I am at a loss for words to describe what just happened All the "undone" elements became one colour and the original column was even more corrupt. The second table G11:I17 was created by repeating the original process to accurately reflect the expected colour scheme. It was not a simple copy/paste of the original table. Actual Results: The colour formatting doesn't represent the range of numbers present in the sample. It appears to "remember" what may once have been the range of numbers. Saving and reloading almost solved the issue but the colours did not recreate the original condition before the errors occurred. Expected Results: Consistently colour the data that is present in the sample - not what once may have been. Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 6.3.6.2 (x64) Build ID: 2196df99b074d8a661f4036fca8fa0cbfa33a497 CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: sv-SE (en_GB); UI-Language: en-GB Calc: threaded
Created attachment 162837 [details] Tiny Spreadsheet containing colour formatted tables
I am aware that the conditional formatting is "hierarchical" and the latest conditional formatting will take precedence over any pre-existing cell formatting. However, the manner in which it is implemented can sometimes be confusing/inconsistent/undesirable. If a small column of data is conditionally formatted to the default 3 colour scheme it produces a single entry into the "register" of conditional formatting. If the last cell is dragged to fill and increment then the conditional formatting is also dragged to encompass the new range. If the user anticipates extending the data and pre-formats a much greater range by simply managing the existing formatting register then the formatting is as would be expected. However, if a selection of the data is itself cut & pasted into the newly extended array it creates a new formatting schedule with the three colour range covering only the copied data. I would observe that if the selection were pasted to an unformatted array area then whilst that may not be the desired format, the irregularity would remind the user that the conditional formatting range needs extending BUT, if the selection is cut & pasted to a pre-existing range then the users' desire is probably to maintain a consistent format across the entire range. Possibly another instance where a radio button is required to permit the users to indicate their preferences. Special pasting with only the cell values works without damaging the conditional format - but only when special pasting functions as planned. There are a number of exisitng reports defining how it is not working to the users' satisfaction.
(In reply to Colin from comment #0) > Select and delete the values in column H and observe the new colour > rendition – seems consistent. > > Undo the deletion and observe the colour rendition. > > Select and delete the values in column I and observe the unchanged colour > rendition - seems inconsistent. > > Delete Column I observe the colour rendition – what should be consistent? > > Delete Column H observe the colour rendition - what should be consistent? > > Undo the two column deletions – I am at a loss for words to describe what > just happened All the "undone" elements became one colour and the original > column was even more corrupt. I don't reproduce the final corruption. I tried the deleting and undoing in some different sequences. Could you re-test with 7.1.5 or newer? NixOS Version: 7.3.0.0.alpha0+ / LibreOffice Community Build ID: 67e47070a7580a17804adce812cc2f98bfe7b51f CPU threads: 16; OS: Linux 5.13; UI render: default; VCL: x11 Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded
(In reply to Buovjaga from comment #3) > (In reply to Colin from comment #0) > > > I don't reproduce the final corruption. I tried the deleting and undoing in > some different sequences. > > Could you re-test with 7.1.5 or newer? > > NixOS > Version: 7.3.0.0.alpha0+ / LibreOffice Community > Build ID: 67e47070a7580a17804adce812cc2f98bfe7b51f > CPU threads: 16; OS: Linux 5.13; UI render: default; VCL: x11 > Locale: fi-FI (fi_FI.UTF-8); UI: en-US > Calc: threaded My Current System Version: 7.0.6.2 (x64) Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded Which is later than the original but not your prescribed target. In all honesty, I'm loath to change my working system as it cost me a week of heartache last time I tried that. However, I have attached a composite of screen images taken Pre- & Post- testing on my current system, demonstrating both the inconsistent colour rendition and the resultant Conditional Colour Formatting Management Array. My guess would be the manner in which the array is manipulated following insertions and deletions is the culprit. If your identical testing on a later system produces only some identical results - per the ***pre-testing display*** then it could indicate a "partial" fix. I wonder if that was intentional ;). I doubt I tested all permutations of inserting and deleting data and columns and certainly have documented some of the inconsistencies of the Conditional Formatting Array following row insertions. If you never followed the specific instructions for the error I observed then it's feasible there are alternate process flows that produce alternate results
Created attachment 173888 [details] Composite Screen Dump
(In reply to Buovjaga from comment #3) > (In reply to Colin from comment #0) > > > I don't reproduce the final corruption. I tried the deleting and undoing in > some different sequences. > > Just a thought - Have you inspected the conditional colour formatting array to assess whether the changes now seem consistent with the objectives?
(In reply to Colin from comment #4) > If you never followed the specific instructions for the error I observed > then it's feasible there are alternate process flows that produce alternate > results I did follow them, but there is ambiguity (delete H, undo, delete I, delete I (again?), delete H, undo both). You can test with Win-x86_64@tb77-TDF from https://dev-builds.libreoffice.org/daily/master/current.html it will install separately without messing up your stable version.
(In reply to Buovjaga from comment #7) > (In reply to Colin from comment #4) > > If you never followed the specific instructions for the error I observed > > then it's feasible there are alternate process flows that produce alternate > > results > > I did follow them, but there is ambiguity (delete H, undo, delete I, delete > I (again?), delete H, undo both). > > You can test with Win-x86_64@tb77-TDF from > https://dev-builds.libreoffice.org/daily/master/current.html it will install > separately without messing up your stable version. Select and delete the values in column I and observe the unchanged colour > rendition - seems inconsistent. > > Delete Column I observe the colour rendition – what should be consistent? Subtle difference, not ambiguous - Select and delete the VALUES* in column I (or H) is not the same as Delete Column I (or H). I thought identifying Values and Columns signified the differences. Is there a preferred nomenclature to obviate future misunderstandings? * Not shouting - emphasising. I shall try again to create a testing environment with your link - could you also verify whether the difference between the two testing strategies resulted in your alternate results?
(In reply to Colin from comment #8) > (In reply to Buovjaga from comment #7) > > (In reply to Colin from comment #4) > > > If you never followed the specific instructions for the error I observed > > > then it's feasible there are alternate process flows that produce alternate > > > results > > > > I did follow them, but there is ambiguity (delete H, undo, delete I, delete > > I (again?), delete H, undo both). > > > > You can test with Win-x86_64@tb77-TDF from > > https://dev-builds.libreoffice.org/daily/master/current.html it will install > > separately without messing up your stable version. > > Select and delete the values in column I and observe the unchanged colour > > rendition - seems inconsistent. > > > > Delete Column I observe the colour rendition – what should be consistent? > > Subtle difference, not ambiguous - Select and delete the VALUES* in column I > (or H) is not the same as Delete Column I (or H). I thought identifying > Values and Columns signified the differences. Is there a preferred > nomenclature to obviate future misunderstandings? Yeah, I was deleting values. I mean the order of deleting & undoing was a bit ambiguous, so I tried a few different sequences.
(In reply to Buovjaga from comment #9) > (In reply to Colin from comment #8) > > (In reply to Buovjaga from comment #7) > > > (In reply to Colin from comment #4) > > Yeah, I was deleting values. I mean the order of deleting & undoing was a > bit ambiguous, so I tried a few different sequences. Do you get the same results as my screen dumps if you follow the procedure of deleting values and then columns and then undoing the previous actions? It seems quite significant, as the additional entries in the CCF Management array suggest.
(In reply to Colin from comment #10) > (In reply to Buovjaga from comment #9) > > (In reply to Colin from comment #8) > > > (In reply to Buovjaga from comment #7) > > > > (In reply to Colin from comment #4) > > > > > Yeah, I was deleting values. I mean the order of deleting & undoing was a > > bit ambiguous, so I tried a few different sequences. > > Do you get the same results as my screen dumps if you follow the procedure > of deleting values and then columns and then undoing the previous actions? > It seems quite significant, as the additional entries in the CCF Management > array suggest. Oh, sorry, I didn't understand I needed to delete full columns there! I kept deleting values. Now I reproduce. NixOS Version: 7.3.0.0.alpha0+ / LibreOffice Community Build ID: 67e47070a7580a17804adce812cc2f98bfe7b51f CPU threads: 16; OS: Linux 5.13; UI render: default; VCL: x11 Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded
(In reply to Buovjaga from comment #11) > (In reply to Colin from comment #10) > > (In reply to Buovjaga from comment #9) > > > (In reply to Colin from comment #8) > > > > (In reply to Buovjaga from comment #7) > > > > > (In reply to Colin from comment #4) > > > > > > > > Yeah, I was deleting values. I mean the order of deleting & undoing was a > > > bit ambiguous, so I tried a few different sequences. > > > > Do you get the same results as my screen dumps if you follow the procedure > > of deleting values and then columns and then undoing the previous actions? > > It seems quite significant, as the additional entries in the CCF Management > > array suggest. > > Oh, sorry, I didn't understand I needed to delete full columns there! I kept > deleting values. Now I reproduce. > Nothing to apologise for. Deleting & Deleting could easily signify the same thing. Perhaps it's better to use "Erase" for data deletion and "Delete" for column or row deletions. Would that have been more specific?
(In reply to Colin from comment #12) > Nothing to apologise for. Deleting & Deleting could easily signify the same > thing. Perhaps it's better to use "Erase" for data deletion and "Delete" for > column or row deletions. Would that have been more specific? Maybe most specific would have been "Right-click column header and select Delete Columns" :)
(In reply to Buovjaga from comment #13) > (In reply to Colin from comment #12) > > Nothing to apologise for. Deleting & Deleting could easily signify the same > > thing. Perhaps it's better to use "Erase" for data deletion and "Delete" for > > column or row deletions. Would that have been more specific? > > Maybe most specific would have been "Right-click column header and select > Delete Columns" :) Classic case of "Familiarity breeds contempt". For me, the essay at the beginning of the report seemed to signify working with entire columns even though the data was only a few rows and I assumed then that everybody would be on "my page" and would understand that I was referring to "Right-click column header and select Delete Columns" :) In my defence, the original worksheet in which I discovered the error is five pages with thousands of lines, hundreds of columns, and tens of conditional format arrays evolved and modified over a couple of year's worth of data collection. I just tried to simplify it :))). Failed miserably.
CCF = Conditional Colour Format >Format>Conditional>Colour&Scale CCF Array = the definition array >Format>Conditional>Manage I think it would be prudent for somebody to define precisely what the convention should be when a CCF protocol has been applied to a CELL a COLUMN or a ROW that is being copied or inserted or deleted. It is my perception that the colour formatting should either NOT be propagated to the modified array with the onus on the user to update the CCF Array or should duplicate the protocol with a new element in the CCF Array, retaining the existing criteria but operating intuitively upon the (additional) CCF array. 1 2 3 4 5 6 7 8 9 10 in array column or row 1 has the indicated minimum and maximum 4 5 6 7 8 9 10 11 12 13 in array column or row 2 has the indicated minimum and maximum at NO time should the presumption be that the global minimum is 1 and the global maximum is 12 and that 4 5 6 7 8 9 & 10 in both arrays should carry identical colours. Obviously, I'm referring to the insertion of new array elements. If the user has defined a two-dimensional array to have an overall CCF Protocol then we must be able to define/detect this scenario NOT simply presume it when the array is amended. Clearly, when a two-dimensional array is being extended by the insertion of new cells/columns/rows it requires an analysis of the original protocol to define the likely user anticipation. Warning the user that changing the boundaries of an array with conditional formatting requires the user to re-define the conditional formatting places the onus on the user to remedy the conundrum when changing the structure - The Simplest Solution (Workaround). If the array is multiple rows and columns then; If formatting is detected in a row by row order it is likely the user wishes the extended boundary to be the limit and the protocol applied over all cells in the array so, if there are 10 rows/10 columns and a ROW is inserted at row 6 and all columns have their own min & max then each column should have its CCF Array element adjusted. 10 columnar adjustments which now contain 11 cells. Likewise, if the formatting is column by column and a new column is inserted then the user is likely to want the extended columns to be rationalised in the CCF Array by the extension of each existing CCF protocol. Now for the trap. If a row is inserted into a columnar priority protocol or a column is inserted into a row priority protocol then the CCF Array will require new elements with the appropriate row or column protocol. It is my experience that insertions and deletions into data arrays supplemented by a CCF Array are only predictable by their unpredictability.
Bibisected with Linux 43all repo to range e19f1afb2c253944968f85b963934a60b87f472a...3cf91a21fc5089fb7f051bf8a04d2049da88179f Out of which this seems relevant: https://git.libreoffice.org/core/commit/a3c4ee1653166ee2ac1f1b9d65ff1065b6288ebc update cond formats for deleting, moving and cut&paste Steps: 1. Open attachment 162837 [details] 2. Select H1:H7 and delete values 3. Undo Bad result is flat colour range after undo.
Dear Colin, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Version: 7.4.7.2 (x64) / LibreOffice Community Build ID: 723314e595e8007d3cf785c16538505a1c878ca5 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded Still present. Examining the MENU>Format>Conditional>Manage register identifies how additional CF lines are added by the "undo last action" rotor after all the instructions in the initial report are completed
Version: 7.5.7.1 (X86_64) / LibreOffice Community Build ID: 47eb0cf7efbacdee9b19ae25d6752381ede23126 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded Still apparent also refer to bug 143610 for the latest impact following what appears to be a partial remedial release