Ask Your Question

How Can I copy paste Calc formulas with absolute references

asked 2012-12-11 17:06:54 +0100

rrbd gravatar image

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 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?

edit retag flag offensive close merge delete



So 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..

qubit gravatar imagequbit ( 2012-12-13 04:34:06 +0100 )edit

There is an enhancement request if you are interested:

chtfn gravatar imagechtfn ( 2017-10-12 02:34:29 +0100 )edit

6 Answers

Sort by » oldest newest most voted

answered 2012-12-12 01:59:45 +0100

ROSt52 gravatar image

As 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.

edit flag offensive delete link more


Nifty! 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."

vbas96 gravatar imagevbas96 ( 2012-12-12 05:37:00 +0100 )edit

answered 2015-07-01 11:54:04 +0100

pierre-yves samyn gravatar image


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


edit flag offensive delete link more

answered 2012-12-12 00:29:41 +0100

m.a.riosv gravatar image

updated 2012-12-12 00:30:32 +0100

Try 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.

edit flag offensive delete link more


Or 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$1

Timur gravatar imageTimur ( 2015-01-28 10:55:33 +0100 )edit

answered 2012-12-12 04:14:36 +0100

vbas96 gravatar image

updated 2012-12-12 06:25:11 +0100

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.

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!

edit flag offensive delete link more


Very nice. Just to replace =$Sheet.$A$1 with =Sheet.$A$1

Timur gravatar imageTimur ( 2015-01-28 10:56:33 +0100 )edit

answered 2015-06-30 17:06:03 +0100

mimooh gravatar image

updated 2015-06-30 17:32:12 +0100

The whole thing of address rewriting comes during consecutive
(1) cut
(2) paste

You need to interrupt the consecutiveness by putting something in-between:
(1) cut
(2) select some empty cells (more than 1) and drag(move) them
(3) paste

Step (2) is where the cell that it's willing to update itself stops tracking where the other cell is going to be pasted.

edit flag offensive delete link more

answered 2012-12-13 18:51:48 +0100

rrbd gravatar image

Hi 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.



edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2012-12-11 17:06:54 +0100

Seen: 22,700 times

Last updated: Jul 01 '15