Help with copying formula

  • Thread starter Thread starter John Calder
  • Start date Start date
J

John Calder

Hi

I run Excel 2K

I have typed the following formulas in three consecutive cells.

=SUM('DRO BY SHIFT'!Z10:Z12)+BP10
=SUM('DRO BY SHIFT'!Z13:Z15)+BP11
=SUM('DRO BY SHIFT'!Z16:Z18)+BP12

You will note that the Z colum ranges take in 3 cells each time.

The problem is that when I drag or copy these formulas down the column I
dont get the reltive ranges that I need. That is the next one down should read

=SUM('DRO BY SHIFT'!Z19:Z21)+BP13

However I get

=SUM('DRO BY SHIFT'!Z17:Z19)+BP13

How can I write the formula so that when I drag it down it maintains the 3
cell address that I need?

I have many rows to complete and it would take weeks to write each one
manually

Thanks
 
One way...

Assume you enter the first formula in cell A1

=SUM(INDEX(Z$10:Z$100,ROWS(A$1:A1)*3-3+1):INDEX(Z$10:Z$100,ROWS(A$1:A1)*3),BP10)

Copy down as needed. Adjust for the correct end of range Z$100
 
In your startcell where you have this:
=SUM('DRO BY SHIFT'!Z10:Z12)+BP10
Click to expand...

Replace it with this:
=SUM(OFFSET('DRO BY SHIFT'!$Z$10:$Z$12,ROWS($1:1)*3-3,,))+BP10
Then just copy it down as far as required to derive the desired results

Success? Celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
 
Well Done !

Thanks to you both

John


Max said:
In your startcell where you have this:

Replace it with this:
=SUM(OFFSET('DRO BY SHIFT'!$Z$10:$Z$12,ROWS($1:1)*3-3,,))+BP10
Then just copy it down as far as required to derive the desired results

Success? Celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
Click to expand...
 
Back
Top