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: 4.1.0.4 release
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.
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.
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.
I just tested your workaround and it does not work. The second paste does shift the coordinates. Cheers
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.
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.
enhancement request
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/
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.
No further input so I'll remove needsUX flag. IMHO, the de/formularizer would be an interesting enhancement and a cool GSoC project.
To clarify the cut/paste workaround, the way to get a copy between documents (or locations within a single document) while retaining the existing copy is to: 1. Select the cells. 2. Cut. 3. Undo. 4. Move to new location. 5. Paste. Unfortunately, this is a non-obvious hack (although one I have been using for years), and still only works for a single paste. Subsequent pastes are treated as copies, as noted in previous comments. A real solution to this would be incredibly helpful for the use cases proposed 5 years ago, which I encounter regularly in my work.
Eike taught me lately the Sheet > Cycle Cell Reference Type (F4) command that iterates over reference types (=$A$1, =A$1, =$A1, =A1). So I guess he recommends to close this issue as WF (too many functions; keep it simple) resp. WFM (change the reference type before copying).
Erm.. cycle reference types isn't a solution to this specific request though if there are more than one formula cells involved or a formula has a mix of absolute/relative references that is to be preserved.