Ask Your Question
0

calc - copy formulae without adjusting

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

DiesNuts 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

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

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

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

pierre-yves samyn gravatar image

Hi

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

Regards

edit flag offensive delete link more

Comments

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

DiesNuts gravatar imageDiesNuts ( 2016-12-08 11:30:42 +0100 )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 +0100 )edit
0

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

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

Comments

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 +0100 )edit

Please share a sample simple to test a solution.

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

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

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

edit flag offensive delete link more

Comments

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

DiesNuts gravatar imageDiesNuts ( 2016-12-09 08:40:04 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 229 times

Last updated: Dec 09 '16