Copying formula

M

MKH Eyrbakk

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?
 
B

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

MKH Eyrbakk

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top