Copying formula

[Guest]

I have a formula in a worksheet with two sheets, A and B
The formula is in SheetA =SheetB!G8-SheetB!G7+SheetB!G9+SheetB!G10
How can I copy this formula down in SheetA but always getting information 13
rows down in SheetB and then again 13 rows down?

 

[Bob Phillips]

=OFFSET(SheetB!$G$8,(ROW(A1)-1)*13,0)-
OFFSET(SheetB!$G$7,(ROW(A1)-1)*13,0)+
OFFSET(SheetB!$G$9,(ROW(A1)-1)*13,0)+
OFFSET(SheetB!$G$10,(ROW(A1)-1)*13,0)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Something is still wrong . What does (ROW(A1)-1) mean? The formula is in
SheetA.

 

[David Biddulph]

On the first occurrence of the formula, (ROW(A1)-1) will return 1 from the
ROW() function, as the row number for cell A1 is 1, and thus (ROW(A1)-1)
will give zero, hence the offset function will off set by zero rows from the
base address given.
When you copy that down a row, the formula becomes
=OFFSET(SheetB!$G$8,(ROW(A2)-1)*13,0)- OFFSET(SheetB!$G$7,(ROW(A2)-1)*13,0)+
OFFSET(SheetB!$G$9,(ROW(A2)-1)*13,0)+ OFFSET(SheetB!$G$10,(ROW(A2)-1)*13,0)
In this case the row number for cell A2 is 2, so (ROW(A2)-1) gives 2-1 which
is 1. This is multiplied by the 13 to offset each cell reference down by 13
rows, which is what you asked for.

It is not very informative to say "something is wrong". We can't see over
you shoulder so you'll need to tell us what the problem is.

 

[Guest]

Thank you very much for your assistance. Now all is working.
Best regards,
MKH