Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 12 Jul 2020 22:06:23 +0200How Can I copy paste Calc formulas with absolute referenceshttps://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.
In [Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references](https://bugs.freedesktop.org/show_bug.cgi?id=45385) I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document.
Is there any way to reach my goal in a comfortable way?
Tue, 11 Dec 2012 17:06:54 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/Comment by chtfn for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=134368#post-id-134368There is an enhancement request if you are interested: https://bugs.documentfoundation.org/show_bug.cgi?id=78074Thu, 12 Oct 2017 02:34:29 +0200https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=134368#post-id-134368Comment by qubit for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=9023#post-id-9023So what you're asking for is a copy-special option so that you can copy just the contents of a cell, rather than copying a formula with linked/implied references, etc..Thu, 13 Dec 2012 04:34:06 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=9023#post-id-9023Answer by xerostomus for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=254869#post-id-254869If I understand you properly. You need to preserve all links while copying an area?
I had to make a verbatim copy of an source area with various formulas (say A1:E20) just below it.
Target is say G1:K20
A) copy source area: A1:E20.
B) open a new blank sheet
C) paste it on A1 (the same place as in original sheet)
D) select it (in the new sheet) and drag it with a mouse on G1
E) copy it
F) return back to the original sheet
G) paste it on G1
Ufff. Crazy but it works all the references (absolute/relative) are pointing at the same place. :-)
If there are 'file:///home/xerostomus/documents/experiment.ods'#
I just let find this text and replaced it with"" and so it restored the original references. But I had to have the same names of sheets in the new document.
Have you tried to import a whole sheet?Sun, 12 Jul 2020 22:06:23 +0200https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=254869#post-id-254869Answer by m.a.riosv for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=8938#post-id-8938Try with Find & Replace after copy-paste to delete the path:
Find: (.*)'.*'#(.*)
Replace: $1$2
More options: mark regular expressions.
Look in the help to kown more about regular expressions.Wed, 12 Dec 2012 00:29:41 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=8938#post-id-8938Comment by Timur for <p>Try with Find & Replace after copy-paste to delete the path: </p>
<p>Find: (.<em>)'.</em>'#(.*) <br>
Replace: $1$2 <br>
More options: mark regular expressions.</p>
<p>Look in the help to kown more about regular expressions.</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=45512#post-id-45512Or simply, after the paste of formulas to the new file, which look like:
='file:///C:/path/Filename.ods'#$Sheet.$A$1
do Edit - Find&Replace of
'file:///C:/path/Filename.ods'#
and Replace with
nothing (empty string).
Formula that remains is just =$Sheet.$A$1Wed, 28 Jan 2015 10:55:33 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=45512#post-id-45512Answer by vbas96 for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=8952#post-id-8952I'm getting a somewhat different take on this problem than either of my international counterparts. My question for Rainer is, "Why don't you change the absolutes to relatives and let the problem solve itself." Well, at least partially.
In your formula you have the SheetName referenced as "$Tabelle1." The default in LibreCalc makes that an absolute reference, forcing the program always to look back to the original source workbook. Remove the reference-lock ($) so the formula reads simply "Tabelle.1" and when you paste into the destination workbook, you'll have a relative reference which looks for the data in a comparable sheet there.
But we're not done yet. What is meant by a "comparable" sheet? The receiving sheet (where you will paste the formulae) must have the same relative positioning in both workbooks. In the source workbook let's suppose the sheet from which you are copying is separated from Tabelle.1 by two other sheets. In the destination workbook, *the sheet into which you are pasting must also be separated by two sheets* from the worksheet equivalent to Tabelle.1 -- in the same relative position.
Spread the joy!Wed, 12 Dec 2012 04:14:36 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=8952#post-id-8952Comment by Timur for <p>I'm getting a somewhat different take on this problem than either of my international counterparts. My question for Rainer is, "Why don't you change the absolutes to relatives and let the problem solve itself." Well, at least partially.</p>
<p>In your formula you have the SheetName referenced as "$Tabelle1." The default in LibreCalc makes that an absolute reference, forcing the program always to look back to the original source workbook. Remove the reference-lock ($) so the formula reads simply "Tabelle.1" and when you paste into the destination workbook, you'll have a relative reference which looks for the data in a comparable sheet there.</p>
<p>But we're not done yet. What is meant by a "comparable" sheet? The receiving sheet (where you will paste the formulae) must have the same relative positioning in both workbooks. In the source workbook let's suppose the sheet from which you are copying is separated from Tabelle.1 by two other sheets. In the destination workbook, <em>the sheet into which you are pasting must also be separated by two sheets</em> from the worksheet equivalent to Tabelle.1 -- in the same relative position.</p>
<p>Spread the joy!</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=45513#post-id-45513Very nice. Just to replace =$Sheet.$A$1 with =Sheet.$A$1Wed, 28 Jan 2015 10:56:33 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=45513#post-id-45513Answer by pierre-yves samyn for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=52642#post-id-52642Hi
As I do not like to change the source (remove `=` or add text) here is how I proceed:
`Click` in the formula bar, `Ctrl+A`, `Ctrl+C`, `Esc`
That's it just paste, the formula is not changed...
RegardsWed, 01 Jul 2015 11:54:04 +0200https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=52642#post-id-52642Answer by mimooh for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=52605#post-id-52605The whole thing of address rewriting comes during consecutive<br>
(1) cut<br>
(2) paste<br>
You need to interrupt the consecutiveness by putting something in-between:<br>
(1) cut<br>
(2) select some empty cells (more than 1) and drag(move) them<br>
(3) paste<br>
Step (2) is where the cell that it's willing to update itself stops tracking where the other cell is going to be pasted.Tue, 30 Jun 2015 17:06:03 +0200https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=52605#post-id-52605Answer by rrbd for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=9047#post-id-9047Hi all,
Thank you for your hints. I think I will use the "deformularize" trick, it seems the most easy and reliable one. Even if the number of Sheets differs in the documents this will work
The "$Tabelle1." to "Tabelle1." trick also might work often.
I did not understand the regular expressions trick, I will try to understand some more details of RE next weekend.
Thanks
RainerThu, 13 Dec 2012 18:51:48 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=9047#post-id-9047Answer by ROSt52 for <p>Very often I use formulas looking like
=SUMIF($Tabelle1.$AH$242:$AH$389;$B86;$Tabelle1.$M$242:$M$389)
I use many spreadsheet documents all with the same sheet structure, and when I have improved the usability of the area with such a formula I would like to use this improvement in other documents with the same structure. Unfortunately when I copy/paste the cells (or import the complete improved sheets) the formula changes to something like "=SUMMEWENN('file:///C:/Users/Public/....." referring to the source document.</p>
<p>In <a href="https://bugs.freedesktop.org/show_bug.cgi?id=45385">Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references</a> I learned that that is intended, unfortunately I never need that intended behavior, but simply copy/paste the formulas as they are so that they refer to Sheets and cells in the new document. </p>
<p>Is there any way to reach my goal in a comfortable way?</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=8945#post-id-8945As you use in your spreadsheets the same structure a possibility is to
1 - add a character before the "=" sign in front of the formula e.g. a=sumif(.........)
2 - copy and paste the cell content, which is not a formula anymore
3 - remove the character in front of the "=" sign
I use this trick often to avoid any problems with wrong links to cells.Wed, 12 Dec 2012 01:59:45 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?answer=8945#post-id-8945Comment by vbas96 for <p>As you use in your spreadsheets the same structure a possibility is to </p>
<p>1 - add a character before the "=" sign in front of the formula e.g. a=sumif(.........)</p>
<p>2 - copy and paste the cell content, which is not a formula anymore</p>
<p>3 - remove the character in front of the "=" sign</p>
<p>I use this trick often to avoid any problems with wrong links to cells.</p>
https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=8953#post-id-8953Nifty! I've always removed the = sign to deformularize, but inserting a character is more elegant as it simplifies the reversal if you're copying (and pasting) a table of formulae. NatÃ¼rlich care is taken that there's no conflict in the formula that would interfere with the bulk "Find and Replace."Wed, 12 Dec 2012 05:37:00 +0100https://ask.libreoffice.org/en/question/8925/how-can-i-copy-paste-calc-formulas-with-absolute-references/?comment=8953#post-id-8953