Bug 133027 - LO Calc: Conditional Format formula issue, when you "cut-paste" the conditionally formatted cell range into another overlapping range.
Summary: LO Calc: Conditional Format formula issue, when you "cut-paste" the condition...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2020-05-14 08:34 UTC by Tibor Kovács
Modified: 2020-05-15 07:09 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
CF issue demonstration (10.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-14 08:35 UTC, Tibor Kovács
Details
CF issue with non adjacent formatted cell ranges. (11.77 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-14 10:00 UTC, Tibor Kovács
Details
picture about the CF issue (25.32 KB, image/png)
2020-05-14 10:09 UTC, Tibor Kovács
Details
issue with multiple formatted non-adjacent cell ranges (121.29 KB, image/jpeg)
2020-05-14 10:14 UTC, Tibor Kovács
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tibor Kovács 2020-05-14 08:34:09 UTC
Description:
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”


Actual Results:
the CF formula changed by double value relative to the distance of the „paste location-original place”

Expected Results:
the CF formula must be changed same value relative to the distance of the „paste location-original place”


Reproducible: Always


User Profile Reset: No



Additional Info:
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.
Comment 1 Tibor Kovács 2020-05-14 08:35:57 UTC
Created attachment 160808 [details]
CF issue demonstration

LO Calc CF issue demonstration
Comment 2 Tibor Kovács 2020-05-14 08:41:35 UTC
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.
Comment 3 Ming Hua 2020-05-14 09:30:07 UTC
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?
Comment 4 Tibor Kovács 2020-05-14 09:54:52 UTC
"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.)
Comment 5 Tibor Kovács 2020-05-14 10:00:31 UTC
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.
Comment 6 Tibor Kovács 2020-05-14 10:03:10 UTC
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.
Comment 7 Ming Hua 2020-05-14 10:08:56 UTC
(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: 7.0.0.0.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
Calc: threaded

> (I am using Hungarian locale settings, and alternate English/Hungarian user
> interface languages.)
I have Chinese UI here, so locale shouldn't be relevant.
Comment 8 Tibor Kovács 2020-05-14 10:09:51 UTC
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.
Comment 9 Tibor Kovács 2020-05-14 10:14:51 UTC
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.
Comment 10 Ming Hua 2020-05-14 10:19:21 UTC
Also reproducible with 5.2.7:
Version: 5.2.7.2 (x64)
Build ID: 2b7f1e640c46ceb28adf43ee075a6e8b8439ed10
CPU Threads: 2; OS Version: Windows 6.19; UI Render: default; 
Locale: zh-CN (zh_CN); Calc: group
Comment 11 Tibor Kovács 2020-05-15 04:50:53 UTC
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:

E8
E9
E10
E11
E12
E13
E14
E14
E16
E17

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.
Comment 12 Ming Hua 2020-05-15 07:09:59 UTC
(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.