Ask Your Question

calc - copy formulae without adjusting [closed]

asked 2016-12-07 15:05:02 +0200

DiesNuts gravatar image

updated 2020-08-11 17:00:27 +0200

Alex Kemp gravatar image

So I have prepared a summary sheet with some really nasty formulae that reference other sheets in the same file.

In their simplest form, they are of the form


In the uglier versions, they come as


and need to be entered with CTRL+SHIFT+ENTER.

As I have other files that need the same kind of summary but for THEIR sheets, I have named the sheets in these files accordingly.

So I CTRL+A, CTRL+C, switch to the other document and CTRL+V.

Works "great", EXCEPT all the sheet references have been prefixed by the file name (i.e. $mw_rep0 becomes 'file:///home/.../myFirstDocument.ods'#$mw_rep0), which in turn results in most cells displaying Err:502 instead of the calculated values from the new sheet.

How do I copy the sheet WITHOUT doing that transformation? I can't copy-paste each cell's formula one-by-one into all the files that need them because I kind of need to get done before I die of old age.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-11 17:01:51.399432

3 Answers

Sort by » oldest newest most voted

answered 2016-12-07 16:18:43 +0200

pierre-yves samyn gravatar image


I suppose you can find & replace the file name by nothing after the copy...


edit flag offensive delete link more


This actually works. Did not expect that. Thank you.

DiesNuts gravatar imageDiesNuts ( 2016-12-08 11:30:42 +0200 )edit

Unfortunately, it'll only work if you use absolute addressing. Sorry for not mentioning that.

Mike Kaganski gravatar imageMike Kaganski ( 2016-12-11 00:25:05 +0200 )edit

answered 2016-12-09 06:28:17 +0200

Please use Sheet->Move or Copy Sheet... for that.

edit flag offensive delete link more


nope, doesn't work. it still adds the file references.

DiesNuts gravatar imageDiesNuts ( 2016-12-09 08:40:04 +0200 )edit

answered 2016-12-07 18:05:27 +0200

m.a.riosv gravatar image

There are a lof of OFFSET functions in the formulas, remember that OFFSET it's a volatile function, which can means a great slowness using it intensively.
Many times it's possible substitute it with INDEX() that's not volatile.
"volatile means that they are always calculate, no matter if their precedent cells have changed or not"

edit flag offensive delete link more


I am not familiar with the INDEX function. how would you rewrite =AVERAGE(OFFSET($mw_rep0.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep1.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep2.E:E,$B$8,0,$B$9-$B$8,1)) using INDEX?

DiesNuts gravatar imageDiesNuts ( 2016-12-08 11:28:14 +0200 )edit

Please share a sample simple to test a solution.

m.a.riosv gravatar imagem.a.riosv ( 2016-12-08 14:36:50 +0200 )edit

Question Tools

1 follower


Asked: 2016-12-07 15:05:02 +0200

Seen: 406 times

Last updated: Dec 09 '16