How Can I copy paste Calc formulas with absolute references

English
, , , , ,
#1

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?

#2

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…

#3

There is an enhancement request if you are interested: https://bugs.documentfoundation.org/show_bug.cgi?id=78074

#12

Hi

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…

Regards

#4

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.

#5

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

#6

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.

#7

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

#8

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!

#9

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

#10

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.

Thanks

Rainer

#11

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.

#13

If 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. :slight_smile:

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?

#14

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.