Bug 71058 - EDITING: Cut and paste special with transposing does not shift formula links
Summary: EDITING: Cut and paste special with transposing does not shift formula links
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86 (IA32) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Formula Paste-Special
  Show dependency treegraph
 
Reported: 2013-10-30 16:23 UTC by Ralf
Modified: 2019-07-26 21:50 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Example sheet: Cut and paste special with transposing (13.26 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-10-30 16:26 UTC, Ralf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ralf 2013-10-30 16:23:35 UTC
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 shift of formula links does not occur with the command "Paste Special" after cutting cells. The result is unexpected: 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.

The expected and unexpected results are illustrated in the attached spreadsheet "Example-sheet-for-bug-report_Cut-and-paste-special-with-transposing_2013-10-30.ods" with the "Paste Special" option "Transpose". Instead of downloading the sheet, the following workflow can be carried out for illustration and reproduction.

1. Fill cell B6 with the number 4 and cell C6 with the number 7
2. Link cell E6 to cell B6 and link cell F6 to cell C6
3. Mark cells B6:C6 and cut these two cells
4. Select cell B7
5. Paste Special with selection "Paste all", option "Transpose", operation "None" and no shift of cells.

Expected result: Cell E6 is linked with cell B7 and cell F6 is linked with cell B8.

Actual result: Cell E6 is still linked with cell B6 and cell F6 is still linked with cell C6.

I suggest the interim solution to disable "Paste Special" after cutting cells. Disabling "Paste Special" after cutting cells avoids potentially severe loss or change of data due to bug 68976.

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 this bug 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.

I reproduced the unexpected behaviour under Windows 7 and Linux.

I reproduced it with different LibreOffice versions including the most recent pre-release 4.1.3.2. I reproduced it with OpenOffice.org 3.3.0 as well. Hence it is inherited from OpenOffice.org.
Comment 1 Ralf 2013-10-30 16:26:04 UTC
Created attachment 88372 [details]
Example sheet: Cut and paste special with transposing
Comment 2 Cor Nouws 2013-10-30 17:09:16 UTC
Hi Raf,

thanks for the issue. I can confirm it. But it's also related to setting Transposing on or not in my situation.

Regards,
Cor
Comment 3 Ady 2013-10-30 17:17:06 UTC
I actually think the problem might be in “Cut+Paste”, not in 
“Cut+PasteSpecial”.

The formulas in E6 and F6 should “follow” the new position of their 
“Precedents” if you *move* those precedents; e.i. select B6:C6 and *drag* the 
selection to B7:C7.

But if you “Copy+Paste” or “Cut+Paste” or “Copy+PasteSpecial” or 
“Cut+PasteSpecial”, IMO the formulas in E6 and F6 should remain as they were 
before, pointing to the same “Precedent” cells, B6 and C6 respectively.

I think that the respective behaviors should be confirmed with some other 
spreadsheet software of reference (like Excel for example) and then, perhaps, 
changed in LibreOffice accordingly.
Comment 4 Ralf 2013-10-30 19:09:07 UTC
(In reply to comment #2)
> Hi Raf,
> 
> thanks for the issue. I can confirm it. But it's also related to setting
> Transposing on or not in my situation.
> 
> Regards,
> Cor

Hi Cor,

I double-checked and agree with you. The behaviour of "Cut" and "Paste Special" is only unexpected with the option "Transpose". Obviously I missed this point when writing the bug report.

This fact implies that my suggested interim solution has disadvantages. 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.

Best regards, Ralf
Comment 5 Ralf 2013-10-30 19:16:42 UTC
(In reply to comment #3)
> I actually think the problem might be in “Cut+Paste”, not in 
> “Cut+PasteSpecial”.
> 
> The formulas in E6 and F6 should “follow” the new position of their 
> “Precedents” if you *move* those precedents; e.i. select B6:C6 and *drag*
> the 
> selection to B7:C7.
> 
> But if you “Copy+Paste” or “Cut+Paste” or “Copy+PasteSpecial” or 
> “Cut+PasteSpecial”, IMO the formulas in E6 and F6 should remain as they were 
> before, pointing to the same “Precedent” cells, B6 and C6 respectively.
> 
> I think that the respective behaviors should be confirmed with some other 
> spreadsheet software of reference (like Excel for example) and then,
> perhaps, 
> changed in LibreOffice accordingly.

Hi Ady,

I think that as well "Copy"/"Paste" as "Cut"/"Paste" behave well and as expected in LibreOffice. MS Excel does not behave differently.

We agree that the links should not change after "Copy"/"Paste".

However, I think that it is convenient that the links follow the cell in case of "Cut"/"Paste" like when "moving" or "dragging" the cells with the cursor. A large number of links can point to a single cell. If one wants to move this cell to another position in the spreadsheet without the mouse (e.g. to another worksheet), one can simply move the cell with "Cut"/"Paste". This workflow takes a few seconds also across worksheets, and the large number of links is adjusted accordingly without any effort. If "Cut"/"Paste" behaved like "Copy"/"Paste", the large number of links would have to adjusted manually.

Best regards, Ralf
Comment 6 Cor Nouws 2013-10-30 20:02:39 UTC
(In reply to comment #5)

> If
> "Cut"/"Paste" behaved like "Copy"/"Paste", the large number of links would
> have to adjusted manually.


Yes, Select+move works as Cut&Paste
Comment 7 Cor Nouws 2013-10-30 20:08:31 UTC
(In reply to comment #4)

> I double-checked and agree with you. The behaviour of "Cut" and "Paste
> Special" is only unexpected with the option "Transpose". Obviously I missed
> this point when writing the bug report.

No problem.

> This fact implies that my suggested interim solution has disadvantages.
> 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.

I think so yes..
Currently I see two options
1. just set a request to change the behaviour: Selecting Transpose should not affect the pasting of formulas.
2. as the UX-list...

Think what would happen if we change as in option 1..
Would that rip users of the posibility of transposing without having formulas untouched? 
Of course the experienced user (and those using transposing are) could also use an extra step in another sheet??
Ideas?
Comment 8 Ady 2013-10-30 20:25:54 UTC
(In reply to comment #5)
> Hi Ady,
> 
> I think that as well "Copy"/"Paste" as "Cut"/"Paste" behave well and as
> expected in LibreOffice. MS Excel does not behave differently.

Perhaps I wasn't clear enough. IMO, "Cut+Paste" is _not_ behaving "well" in 
Calc (just as bug #68976 describes too), and I think Excel (and others) indeed 
behaves differently.

For example, you could perform one "Cut" task, and no "Paste" anywhere, just 
save the file and close it. The "Dependents" would have no clear and definitive 
formula for such case.

In other words, a "Move Cell" action (a.k.a. "Select a cell and drag it to a 
different position") should not be the same as "Cut+Paste". When I *move* a 
cell, all "Dependents" of that cell keep track of it, thus correcting their 
respective formulas. That's not the same as "Cut+Paste" (at least, not in other 
spreadsheet tools). And since other spreadsheet tools (including popular ones) 
don't track such "Cut+Paste"-ed cells (their prior dependents keep their prior 
formulas intact), IMHO Calc should do the same.

Hence, if there is a bug to be considered, then it is in the "Cut+Paste" action 
behaving as a "Move" (e.i. drag cell or range of cell) action. This corresponds 
to the initial description of bug #68976, "Formulas change after cut and paste 
action".

I haven't tested each and all possible "Cut+PasteSpecial" possibilities, but 
generally speaking I would say that "Cut+Paste" in Calc should behave more as 
"Cut+PasteSpecial" in Calc (as in other spreadsheet tools), not the other way 
around. Perhaps there is an issue regarding “Transpose” too, but, as I said, I 
haven't specifically tested all “PasteSpecial” options before posting here.


Regards,
Ady.
Comment 9 Ralf 2013-10-30 21:29:26 UTC
(In reply to comment #7)
> Currently I see two options
> 1. just set a request to change the behaviour: Selecting Transpose should
> not affect the pasting of formulas.
> 2. as the UX-list...
> 
> Think what would happen if we change as in option 1..
> Would that rip users of the posibility of transposing without having
> formulas untouched? 
> Of course the experienced user (and those using transposing are) could also
> use an extra step in another sheet??
> Ideas?

Unfortunately, I do not get the hang of these two options. But you ask for ideas, so let me give it a try.

Option A (nice and clean): LO Calc produces the expected result, which is described in the bug request and illustrated in the attached ODS spreadsheet. Ideally bug 68976 is fixed as well. I consider bug 68976 to be more severe than this one.

Option B (quick and dirty): If cells are cut and if the "Paste Special" menu is opened subsequently, the option "Transpose" is shaded grey in the "Paste Special" menu and cannot be selected like after copying cells.

I don't know how quickly option B could be implemented, but it is not as dirty as it might appear at first sight for three reasons:

a) The change or loss of data of bug 68976 is avoided.

b) No functionality of LO Calc is lost. "Copy" and then "Paste Special" with option "Transpose" works properly. The current behaviour of "Cut" and then "Paste Special" with option "Transpose" does not add functionality. It confuses (see this bug) or even leads to data loss or change (see bug 68976).

c) It does not fall back behind MS Excel 2007 (I don't know about other Excel versions). If you cut cells with MS Excel and if you want to paste with options afterwards, the option to transpose is shaded grey and cannot be selected.

So option B would fix something without losing anything. However, it's not great like option A.

Does option 1 or 2 coincide with option A or B? Maybe I miss the point.
Comment 10 QA Administrators 2015-04-01 14:42:05 UTC Comment hidden (obsolete)
Comment 11 Ralf 2015-04-12 18:32:14 UTC
(In reply to QA Administrators from comment #10)

The bug is still present without changes (LibreOffice 4.4.2.2, Windows 7).
Comment 12 tommy27 2016-04-16 07:28:17 UTC Comment hidden (obsolete)
Comment 13 Ralf 2016-04-16 08:46:27 UTC
(In reply to tommy27 from comment #12)

The bug is still present without changes in version 5.1.2.2 (x64) with Windows 7.
Comment 14 QA Administrators 2017-05-22 13:26:08 UTC Comment hidden (obsolete)
Comment 15 Ralf 2017-05-22 19:27:47 UTC
(In reply to QA Administrators from comment #14)

The bug is still present without changes in version 5.2.7.2 (x64, Windows 7).
Comment 16 QA Administrators 2018-06-30 02:40:38 UTC Comment hidden (obsolete)
Comment 17 Ralf 2018-07-15 07:00:43 UTC
(In reply to QA Administrators from comment #16)

The bug is still present without changes in version 6.1.0.1 (x64, Windows 10).
Comment 18 QA Administrators 2019-07-16 02:46:47 UTC Comment hidden (obsolete)
Comment 19 Ralf 2019-07-26 21:50:22 UTC
The bug is still present without changes in version 6.3.0.2 (x64, Windows 10).