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.