Calc - copy formulae without adjusting

[DiesNuts]

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

=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))

In the uglier versions, they come as

=PERCENTILE(OFFSET($mw_rep0.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep1.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep2.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.E:E,$B$8,0,$B$9-$B$8,1),0.9)

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.

[PYS]

Hi

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

Regards

[DiesNuts]

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

[mikekaganski]

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

[mariosv]

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”

[DiesNuts]

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?

[mariosv]

Please share a sample simple to test a solution.

[mikekaganski]

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

[DiesNuts]

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