Download it now!
Bug 97956 - Don't fragment A:A conditional formatting when rows/columns are moved
Summary: Don't fragment A:A conditional formatting when rows/columns are moved
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Conditional-Formatting-Managing
  Show dependency treegraph
Reported: 2016-02-18 01:17 UTC by 7qia0tp02
Modified: 2019-08-03 01:19 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

broken conditional formatting (13.37 KB, image/png)
2016-02-18 01:17 UTC, 7qia0tp02
This kind of fragmenting is an every day occurrence (44.14 KB, image/png)
2017-11-26 21:12 UTC, 7qia0tp02

Note You need to log in before you can comment on or make changes to this bug.
Description 7qia0tp02 2016-02-18 01:17:03 UTC
Created attachment 122744 [details]
broken conditional formatting

If a conditional format is specified for a column like "F:F" it is stored in Conditional Formatting as "F1:F1048576" rather than "F:F".

If the rows are then reordered using Alt+Drag, the Conditional Formatting breaks and is fragmented into multiple small pieces.

F:F means that it applies to the entire column; don't break it up when things are moved around.
Comment 1 m.a.riosv 2016-02-18 02:04:50 UTC
Please what is your LibreOffice version and your OS, few time ago some work have been done about the behavior of  A:A and 1:1 with some actions, on the other hand there is a request for enhancement to reunify CF condition ranges.

Cell ranges with the same Conditional Formatting should be consolidated to 1 range.

CONDITIONAL FORMATTING: Option for reunify ranges with same conditions
Comment 2 7qia0tp02 2016-02-18 02:11:21 UTC
Build ID: 2b9802c1994aa0b7dc6079e128979269cf95bc78
Locale: en-US (en_US)

Windows 7 Pro 64-bit
Comment 3 m.a.riosv 2016-02-21 00:41:40 UTC
I see now.

Entire column/row was implemented for 5.0,

Maybe Eike can take a look.
Comment 4 Markus Mohrhard 2016-04-18 18:48:17 UTC
We might be able to fix the fragmentation.

However I think it makes no sense to store F:F in that case. It is not a formula reference but a cell range in that case.

Eike might have some additional ideas about F:F vs F1:F1048576 for ScRangeList/ScRange based references.
Comment 5 Aprax 2016-06-23 18:10:09 UTC
Please fix the fragmentation.
How to duplicate:
1 check CF = OK, no 'split ranges' range is A2:A1048576
2 delete a row A1476
3 Undo
4 check CF, where there was one range there are two:


with exactly the same rules.

Actually the real fix is to not split in the first place. 
If I wanted a split, I'd do it myself.

This has been a problem 'forever' and still exists in
W7Pro SP1 64bit
Comment 6 7qia0tp02 2016-06-28 04:59:54 UTC
This is still a problem in on Windows 7 64-bit.  If you try to create a range like C:F in the Conditional Formatting, it accepts it, but then converts it to C1:F1048576, which is both conceptually wrong and difficult to read.

If you then cut and paste rows, or Alt-Drag them up or down to organize them, the Conditional Formatting is broken into separate rules, like one for C25:F1048576,C1:F20 and a different but identical rule for C21:F24.

If you Sort the rows to organize them, the brokenness doesn't happen.

"F:F" does not mean "column F from cell 1 to cell 1048576"; it means "the entire column F".
Comment 7 7qia0tp02 2016-06-28 05:08:15 UTC
> However I think it makes no sense to store F:F in that case. It is not a formula reference but a cell range in that case.

"F:F" means "the entire column F".  That the spreadsheet is actually implemented as a large but finite number of rows (65536 or 1048576) is a programmer's implementation detail, not something that should be exposed to users. Can you reference an entire column in OpenOffice Calc (like A:A in Excel)?
Comment 8 7qia0tp02 2017-11-26 21:12:57 UTC
Created attachment 138002 [details]
This kind of fragmenting is an every day occurrence
Comment 9 7qia0tp02 2018-07-31 04:46:17 UTC
Is there any chance of this ever being fixed? I have to manually edit the conditional formatting spans dozens of times per day for years because of this.  It's very frustrating.  

F:F means "the entire column F".  It does not mean "Column F, rows 1 through 1048576".  The former does not make sense to break up when rows are swapped. It always means "the entire column F" even if you move cells up or down within that column.
Comment 10 QA Administrators 2019-08-01 03:36:13 UTC Comment hidden (obsolete)
Comment 11 7qia0tp02 2019-08-03 01:19:22 UTC
Yes of course it's still a problem, it happens every day.

Version: (x64)
Build ID: 1ec314fa52f458adc18c4f025c545a4e8b22c159
CPU threads: 4; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded