Bug 78074 - Paste Special should have an option to paste raw formulas
Summary: Paste Special should have an option to paste raw formulas
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Blocks: Calc-Enhancements Paste-Special
  Show dependency treegraph
Reported: 2014-04-29 12:14 UTC by stragu
Modified: 2018-11-22 14:21 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description stragu 2014-04-29 12:14:36 UTC
Problem description: 

Currently, there is no way to copy and paste a range of cells containing formulas without changing any cell coordinate or spreadsheet references contained in formulas.

There should be an option in the Paste Special dialogue to let the user paste formulas without having them modified according to the where it is pasted.

This option could be presented as a box to tick, and could be named "paste raw formulas".

This could be useful for example for documents or sheets sharing the same structure and referring to data that shows different values but is organised in the same manner. If a table is enhanced in one spreadsheet, the user might want to copy it and replace the ones in the other sheets/documents.
A table could contain fixed coordinates (using "$") and non-fixed coordinates and the user would like to copy the raw formulas without worrying about the action linking cells from a different spreadsheet or document.

Another case would be that the user simply wants to place a number of formula-containing cells somewhere else in the sheet without getting all the coordinates shifted accordingly, and without having to go back into each formula to add "$" signs.

As this option could result in errors (especially when copying from one document to another with different sheet names), there could be a warning dialogue letting the user know that "This option might produce errors in cells that contain a reference to an object that does not exist in the document". This dialogue could appear only when the action is made from one document to another.
Operating System: All
Version: release
Comment 1 m.a.riosv 2014-04-29 22:02:16 UTC
Hi chtfn, thanks for reporting.

I think you can get it easily, instead of copy, cut and paste in the actual position and in the new one, both will have the same formulas without change in the references.
Comment 2 stragu 2014-04-30 01:21:25 UTC
Hi m.a.riosv

Thanks for the answer. This solution resolves the second case, but not the first one, when the user does not want to delete the source.
Comment 3 m.a.riosv 2014-04-30 02:21:34 UTC
Why not?, cut and paste in the source range before moving to any other range to paste.
It's no perfect but it's simple.
Comment 4 stragu 2014-04-30 05:31:17 UTC
I just tested your workaround and it does not work. The second paste does shift the coordinates.

Comment 5 m.a.riosv 2014-05-01 01:49:00 UTC
I have just verify that it works for the first past, after that it works like copy, what works fine pasting then again in the source range.

Remember there is the option to copy one/several sheets at once in the some or in a new file.
Comment 6 stragu 2014-05-01 02:53:01 UTC
I understand your workaround, but it is extremely unintuitive (cut, paste in target, paste in source).

About copying a sheet from one document to another, a good thing is that it does not link to the source document's cells, but it still shifts the spreadsheet references.

The idea is that, whith that extra option, the formula text does not change at all when the sheets/cells are copied.
Comment 7 tommy27 2014-05-01 07:18:57 UTC
enhancement request
Comment 8 stragu 2017-10-12 00:49:45 UTC
UX Team -- please take a look at this enhancement. Thanks!
Really sorry if this does not relate to UX; I might have misunderstood the team's role.

I wanted to mention that this enhancement would resolve this kind of support question: https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/
Comment 9 Heiko Tietze 2018-11-20 10:34:39 UTC
Rather than introducing another paste special option I would think about the mentioned "deformularizer". The issue is also relevant in other cases:

Given A1 contains a value and A2 "=A1" the copy/paste operation to A3 returns "=A2", which is wanted in most cases but sometimes you need the exact same cell done by "=$A$2". 

The (de)formularizer could toggle fix row/column on/off; in case of the table/sheet reference it would set it to the current sheet when switching on.
Comment 10 Heiko Tietze 2018-11-22 14:21:02 UTC
No further input so I'll remove needsUX flag. IMHO, the de/formularizer would be an interesting enhancement and a cool GSoC project.