When you "cut-paste" the conditionally formatted cell range, the reference of the CF formula go wrong. Using relative references, the formula must populate "paralell" with the distance of the "cut place" and the "paste place". But it not work properly.
Steps to Reproduce:
See the attachment.
1. The red cell E8 was formatted by CF formula with relative reference in the condition: D8=1. The name of the applied Cell Style is „Error”.
2. Then the yellow and the red cell have been copied down to D17.
3. Check the setting of the CF in the Manage Conditional Format feature: The CF formula and range have been integrated into one condition – as it is expected.
4. Select, Cut and then Paste the colorized block (D8:E17) down by some rows. (if you move the range less than 10 rows down you will prompt see the strange behavior)
5. Check again the formula of the CF
6. You will see: the reference of the CF formula changed by double value relative to the distance of the „paste location-original place”
the CF formula changed by double value relative to the distance of the „paste location-original place”
the CF formula must be changed same value relative to the distance of the „paste location-original place”
User Profile Reset: No
The attached file was created in the LO 6.4.3 winPenPack portable version, but the issue is present in my permanently installed LO 6.2.8 and the LO 6.1.6 too.
Created attachment 160808 [details]
CF issue demonstration
LO Calc CF issue demonstration
The issue is present in many LO version (Checked in some 6.x.x versions), and it present in the clean-installed LibreOffice versions (first LO installation on a newly (re)installed operating sytem) too. We are using Win7 x64 Pro and Win10 x64 pro and 64 bit LibreOffice version.
Can't reproduce with either 6.2.8 or 7.0.0 alpha1 on Windows 10.
My exact steps are:
1. Open attached example file;
2. Use mouse-dragging to select D8:E17 range;
3. Edit > Cut;
4. Click in cell D21;
5. Edit > Paste.
Result: colored block are cut-and-pasted successfully with both yellow and red colors, Format > Conditional Formatting > Manage... dialog shows the block has the same condition "D21=1". Using keyboard Ctrl+X and Ctrl+V in steps 3 and 5 makes no difference.
Are you using different actions to cut and paste?
"Are you using different actions to cut and paste?"
Absolutely not. And the issue is present, when I grag the the selected cell range down by two rows.
Try to move the cell range less rows down. Select cell D10 to paste. You will see the issue prompt.
(I am using Hungarian locale settings, and alternate English/Hungarian user interface languages.)
Created attachment 160810 [details]
CF issue with non adjacent formatted cell ranges.
Try again to paste the selected and cutted D8:E17 cellrange (only that cell range!) to the D21 position.
I just uploaded an another version of my sample file. Now, there are two non-adjacent cellranges with same relative condition formula.
Please try again to paste the selected and cutted D8:E17 cellrange (only that cell range!) into the D21 position. Then check the formula.
(In reply to Tibor Kovács from comment #4)
> Try to move the cell range less rows down. Select cell D10 to paste. You
> will see the issue prompt.
Right, clicking in D11 and pasting reproduced the bug. I misunderstood your instruction of "move less than 10 rows". In retrospect it's obvious as overlapping cut and paste areas is a common source of bugs.
My LO version:
Version: 220.127.116.11.alpha1 (x64)
Build ID: 6a03b2a54143a9bc0c6d4c7f1...
CPU 线程: 2; 操作系统: Windows 10.0 Build 18363; UI 渲染: Skia/Raster; VCL: win;
Locale: zh-CN (zh_CN); UI: zh-CN
> (I am using Hungarian locale settings, and alternate English/Hungarian user
> interface languages.)
I have Chinese UI here, so locale shouldn't be relevant.
Created attachment 160811 [details]
picture about the CF issue
Here is a picture about the CF issue (after Cut&Paste) when only one conditionally formatted cell range is present.
Created attachment 160812 [details]
issue with multiple formatted non-adjacent cell ranges
And here is a picture about the issue with multiple formatted non-adjacent cell ranges.
Also reproducible with 5.2.7:
Version: 18.104.22.168 (x64)
Build ID: 2b7f1e640c46ceb28adf43ee075a6e8b8439ed10
CPU Threads: 2; OS Version: Windows 6.19; UI Render: default;
Locale: zh-CN (zh_CN); Calc: group
The behavior of the LO 4.4.7 (I have only a portable version today):
The issue is present in files containing the FC cells created in the LO 6.x.x when I open them in the LO 4.4.7.
But there is not any issue with files created in LO 4.4.7. The LO 4.4.7 can handle the Cut/Paste of the CF-ed range.
And the LO 6.x.x versions can work wellthe CF created in the LO 4.4.7
- - -
A presumed reason:
The LO 4.4.7 can not merge the CF reference of the adjacent cells into one range reference.
The reference list for my first sample file (containing one CF cell range; recreated in LO 4.4.7) is:
And the LO 6.x.x works well with this list, but is can not work with the impoved (joint, combined) "E8:E17" type reference.
(In reply to Tibor Kovács from comment #11)
> A presumed reason:
> The LO 4.4.7 can not merge the CF reference of the adjacent cells into one
> range reference.
Good to know.
I tested 5.2.7 again with manually created CF from empty spreadsheet, that version can already combine adjancent cells with CF into one range, like in your example. So the change happened some time between 4.4 and 5.2.
...Any (good or bad) news about the bug...?
there are atomic fails, at least two:
1.) one cell with a value, one cell referencing that value for conditional format,
moving the referenced! cell by either 'ctrl-x' - 'ctrl-v' or by 'mouse-push' cleanly adapts the relative referencing in the conditional format formula, ok,
moving the referencing! cell either by ctrl-x ctrl-v or by 'mouse-push' does not! adapt the relative referencing in the conditional format formula, not ok,
(the reference points to a new cell with identical relative position to the moved cell as the old 'target', behaviour as it should be for copying! the referencing cell),
2.) (may result from above) moving a range of cells containing as well the conditional formatted cell(s) as the reference(s) for formatting (wich shouldn't be the cell itself, neither referenced by 'cell value' nor by a formula pointing to itself), to an 'overlapping position' (mostly) adapts the formatted range and the relative formula by double the value of the move, with the funny side effect that on move left or down it is evaluated acc. the new wrong references and thus fails, but on move right or up the evaluation is also erroneous and the result looks good ... only looks but isn't as the formula is wrong (see attached screenshot), and as the construction doesn't survive save-load,
tested with 7.1.0 alpha win7x64,
history: ver. 22.214.171.124 didn't adapt relative referencing in any way, 126.96.36.199 did but already has both above fails (a little different as it has different handling for grouping of formattings),
i admit ... programming such stuff is challenging, estimate working with parts of formatted ranges, or parting / breaking referenced ranges ...
boiled down, now it only needs an interested dev ...
Created attachment 166892 [details]
screenshot for previous comment
see erroneous range and formula displayed, doesn't survive save-load,
Is there any hope for a solution????
*** Bug 145146 has been marked as a duplicate of this bug. ***