Bug 68976 - Formulas change after cut and paste special action with Transpose
Summary: Formulas change after cut and paste special action with Transpose
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium major
Assignee: Roland Kurmann
: 104731 (view as bug list)
Depends on:
Blocks: Cell-Formula Paste-Special
  Show dependency treegraph
Reported: 2013-09-05 11:03 UTC by alexjironkin
Modified: 2021-05-13 07:38 UTC (History)
13 users (show)

See Also:
Crash report or crash signature:

example sheet (8.62 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2015-10-26 13:36 UTC, Timur

Note You need to log in before you can comment on or make changes to this bug.
Description alexjironkin 2013-09-05 11:03:04 UTC
I was working with the spreadsheets and noticed this little gem. The value in the formula with the anchored cells (e.g. $A$1) are updated when i cut and paste in unrelated rows.

Steps to reproduce:
Enter value 1234 in cell A1
Enter values 5,2,3 in cells A3, A4 and A5 respectively
Enter formula ($A$1-ABS(A3))*-1 in cell B3
Drag the formula to fill other 2 cells

Now you have values -1229, -1232 and -1231 in cells B3, B4 and B5

Enter values 2, 3, 4 in cells A7, B7 and C7
Select these cells and cut them.
Click on A7 cell
Paste special: Number and Transpose checked, Operations: None, Shift Cells: Don't shift
Values in cells B3, B4 and B5 change to 2, 0 and 1 respectively
Formulas are updated to ($A$7-ABS(A9))*-1, ($A$7-ABS(A4))*-1, ($A$7-ABS(A5))*-1 in those cells.

I am running opensuse 12.1 and official repository and Build ID: 350m1(Build:413)

This also happens on Windows OS
Comment 1 Joel Madero 2013-09-09 04:22:47 UTC
Thank you for reporting this issue! I have been able to confirm the issue on:
Version 4.0.4 
Platform: Bodhi Linux 2.2 x64
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
As I've been able to confirm this problem on an earlier release I am changing the version number as version is the earliest version that we can confirm the bug, we use comments to say that the bug exists in newer versions as well.

Marking as:

New (confirmed)
Major - loss (change) of data
High - default seems appropriate

Indeed - quite the gem ;) Thanks for catching this

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:

There are also other ways to get involved including with marketing, UX, documentation, and of course developing -  http://www.libreoffice.org/get-help/mailing-lists/. 

Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Comment 2 Ralf 2013-10-30 16:30:30 UTC
I suggest the interim solution to disable "Paste Special" after cutting cells.

The advantage is obvious: Loss or change of data are avoided. I experienced the consequences of this bug in a complex spreadsheet. The changes of links can be difficult to detect and to fix again.

I do not see substantial disadvantages of disabling "Paste Special" after cutting cells because formula links are not shifted as expected after cutting and inserting with "Paste Special". I reported this unexpected behaviour with bug 71058 "EDITING: Cut and paste special does not shift formula links as expected". If cells are cut and then pasted normally (i.e. with "Paste" instead of "Paste Special"), all formula links to these cells point to the new cell positions after pasting the cells. This expected shift of formula links does not occur with the command "Paste Special". Formula links still point to the old positions of the moved cells. Therefore, "Cut" and "Paste Special" leads to the same result as "Copy" and "Paste Special" with subsequent deletion of the old cells. Thus disabling "Paste Special" after cutting cells does not lead to a loss of functionality. It can be replaced in the workflow by "Copy" and "Paste Special" with subsequent deletion of the old cells. A loss of functionality occurs only if bug 71058 can be fixed by aligning the behaviour of "Paste Special" with "Paste" after cutting cells. MS Excel 2007 does not offer the full set of "Paste Special" options after cutting cells either. I do not have access to later Excel versions.
Comment 3 Ralf 2013-10-30 19:20:45 UTC
(In reply to comment #2)

I received a comment to bug 71058. The comment implies that my suggested interim solution has disadvantages, because "Cut" and "Paste Special" behaves as expected unless "Transpose" is selected. Therefore, disabling "Paste Special" completely after cutting cells would disable expected behaviour as well. Alternatively, only the "Transpose" option in "Paste Special" could be disabled as interim solution. However, such a quick fix lacks charm admittedly. Maybe there is no convincing interim solution.
Comment 4 Cor Nouws 2013-10-30 23:01:41 UTC
This is a bug.
and an old one...

I clarified the summary - quite essential to mention Paste special and Transpose to help people seeing the issue :)

Comment 5 peterthevicar 2014-09-25 17:18:28 UTC Comment hidden (obsolete)
Comment 6 Jean-Baptiste Faure 2014-10-04 21:03:48 UTC Comment hidden (obsolete)
Comment 7 Bryan Quigley 2014-12-04 20:02:43 UTC
Another example reproducer - https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1388491
Comment 8 satphil 2015-05-19 08:42:12 UTC Comment hidden (obsolete)
Comment 9 satphil 2015-05-19 08:44:42 UTC
(In reply to satphil from comment #8)
> I have also hit this bug using libreoffice-calc 1:4.2.8-0ubuntu2 on Ubuntu
> 1:4.2.8-0ubuntu2 on x86_64.

Sorry, should have read: on Ubuntu 14.04.2 LTS, Trusty Tahr on x86_64.
Comment 10 Timur 2015-10-26 13:36:38 UTC
Created attachment 119968 [details]
example sheet
Comment 11 QA Administrators 2016-11-08 11:52:09 UTC Comment hidden (obsolete)
Comment 12 Ralf 2016-11-08 20:17:48 UTC
(In reply to QA Administrators from comment #11)

The bug is still present (LibreOffice, 32-bit version, Windows 10).
Comment 13 m.a.riosv 2016-12-17 15:08:00 UTC
*** Bug 104731 has been marked as a duplicate of this bug. ***
Comment 14 QA Administrators 2018-07-02 02:35:28 UTC Comment hidden (obsolete)
Comment 15 Timur 2018-07-02 10:45:29 UTC Comment hidden (obsolete)
Comment 16 QA Administrators 2019-07-03 02:41:57 UTC Comment hidden (obsolete)
Comment 17 Timur 2019-07-03 08:45:39 UTC Comment hidden (obsolete)
Comment 18 Andy 2019-07-03 11:13:43 UTC
yes it's still there and it is an especially embarassing problem when data management in a spreadsheet goes berserk. 
Of course the specific actions that make the bug emerge are rare, but once a user happens to meet it, trust in all computations will be severely tarnished, and anguish will set in... often reason enough to move to other software...
Please anyone who could fix this, help!
Comment 19 Roland Kurmann 2021-04-02 07:23:45 UTC

Version: / LibreOffice Community
Build ID: 4f7bc49bce261f1cf206d25f0299fd080a9f5d28
CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: x11
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 20 Roland Kurmann 2021-05-01 15:58:52 UTC
I've found the problem. Working on a solution.
Comment 21 Roland Kurmann 2021-05-13 07:38:41 UTC
I've submitted a patch: https://gerrit.libreoffice.org/c/core/+/115535
Waiting for review