Excel 2007 : Copy/Drag Formula's - Need to Skip Rows

[Rapacious]

Windows 7
Excel 2007

I have to copy formulas down and across an excel worksheet. When I grab the anchor and drag it just copies the next set of rows and, which is wrong. I need it to skip 4 rows each time.

Example:

=SUM('Cost Summary Unit 1'!O14:Z14,'Cost Summary Unit 1'!O15:Z15,'Cost Summary Unit 2'!O14:Z14,'Cost Summary Unit 2'!O15:Z15,'Cost Summary Unit 3'!O14:Z14,'Cost Summary Unit 3'!O15:Z15,'Cost Summary Unit 4'!O14:Z14,'Cost Summary Unit 4'!O15:Z15,'Cost Summary Unit 5'!O14:Z14,'Cost Summary Unit 5'!O15:Z15,'Cost Summary Unit 6'!O14:Z14,'Cost Summary Unit 6'!O15:Z15,'Cost Summary Unit 7'!O14:Z14,'Cost Summary Unit 7'!O15:Z15,'Cost Summary Unit 8'!O14:Z14,'Cost Summary Unit 8'!O15:Z15)/1000

If i click and drag the above down one row I will get:

=SUM('Cost Summary Unit 1'!O15:Z15,'Cost Summary Unit 1'!O16:Z16,'Cost Summary Unit 2'!O15:Z15,'Cost Summary Unit 2'!O16:Z16,'Cost Summary Unit 3'!O15:Z15,'Cost Summary Unit 3'!O16:Z16,'Cost Summary Unit 4'!O15:Z15,'Cost Summary Unit 4'!O16:Z16,'Cost Summary Unit 5'!O15:Z15,'Cost Summary Unit 5'!O16:Z16,'Cost Summary Unit 6'!O15:Z15,'Cost Summary Unit 6'!O16:Z16,'Cost Summary Unit 7'!O15:Z15,'Cost Summary Unit 7'!O16:Z16,'Cost Summary Unit 8'!O15:Z15,'Cost Summary Unit 8'!O16:Z16)/1000

What I want is:

=SUM('Cost Summary Unit 1'!O18:Z18,'Cost Summary Unit 1'!O19:Z19,'Cost Summary Unit 2'!O18:Z18,'Cost Summary Unit 2'!O19:Z19,'Cost Summary Unit 3'!O18:Z18,'Cost Summary Unit 3'!O19:Z19,'Cost Summary Unit 4'!O18:Z18,'Cost Summary Unit 4'!O19:Z19,'Cost Summary Unit 5'!O18:Z18,'Cost Summary Unit 5'!O19:Z19,'Cost Summary Unit 6'!O18:Z18,'Cost Summary Unit 6'!O19:Z19,'Cost Summary Unit 7'!O18:Z18,'Cost Summary Unit 7'!O19:Z19,'Cost Summary Unit 8'!O18:Z18,'Cost Summary Unit 8'!O19:Z19)/1000

[dilipandey]

Hi Rapacious,

Welcome to the forum.

I would suggest you to look into Offset formula and try offsetting (moving) O14 -> to -> O18, post the sample file in case of any issues. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Richard Buttrey]

Hi,

I've just shown the first range, so add the rest of the sheet references in a similar way.
Note that I've assumed this formula is located on row 10 which is why it shows a -10 in after the ROW() function. Just adjust the 10 as appropriate and change it to the row in which the formula is located.

=SUM(OFFSET('Cost Summary Unit 1'!$O$14:$Z$14,(ROW()-10)*4,0))

[Rapacious]

Thanks. Didnt work. When I try to drag/copy the formula to all the rows below, I get the exact same formula each and every time time.

Here is smaller version of what I posted earlier to show what I did......

=SUM(OFFSET('Cost Summary Unit 1'!$B$10:$N$10,(ROW()-6)*4,0))

That line just gets replicated everytime in every row below the top.