Increase cell range by 2 when dragging down

**NBC_Brian** · 06-02-2011, 11:18 AM

If my first cell says "$A1:$A2",my second "$A3:$A4", and my third "$A5:$A6", is there any way to get Excel to recognise this pattern when dragging the formula down?

Because as it is, it will only update each number by 1 not 2.

I know an easy solution would be to insert a blank line before the next line of data, except I can't do this as I have other formulas in the line that DO increase by only an increment of one.

Any ideas?

**arthurbr** · 06-02-2011, 11:32 AM

Hi and welcome to the board

Supposing the first formula is entered in row 1 try

Please Login or Register  to view this content.

**NBVC** · 06-02-2011, 11:33 AM

Are you using those to sum or average in groups of 2?

Perhaps:

=SUM(INDEX(A:A,1+((ROW(A1)-1)*2)):INDEX(A:A,2+((ROW(A1)-1)*2)))

for summing in groups of 2 beginning at A1

**Richard Buttrey** · 06-02-2011, 11:41 AM

Hi,

Please Login or Register  to view this content.

Regards

**NBC_Brian** · 06-02-2011, 12:31 PM

I'm not quite sure how to integrate the formulas you've provided into my current formulas.

I've attached an example spreadsheet. As you'll see, it's the DSUM formulas in Columns C, F, O, & P that are the problems.

Rows 2 & 3 are what I want to see (done manually), but in row 4 (filled in by dragging down the formulas) the Columns mentioned above need to changed.

How do I use what you've provided in those formulas?

**NBVC** · 06-03-2011, 01:20 PM

The pattern is not consistent.

in Row 2 your criteria range is Sheet3!$F$29:$I$30

in Row 3 your criteria range is Sheet3!$F31:$I32

in Row 4 your criteria range is Sheet3!$F32:$I33

so in rows 1 and 2 there is no overlap, but in rows 2 and 3, there is an overlap of row 32 reference....

Please clarify.

If you want to start at row 29 and 30 and then to 31 and 32 and then 33 and 34 etc. then try:

=DSUM(Sheet4!$A:$S,Sheet4!$S$1,INDEX(Sheet3!K:K,29+((ROW(A1)-1)*2)):INDEX(Sheet3!M:M,29+((ROW(A1)-1)*2)+1))

copied down in Column C... similar in other columns.

**NBC_Brian** · 06-03-2011, 02:37 PM

Row 2 should have read $F29:$I30 not $F$29:$I$30 but I purposely left Row 4 wrong (as that's what I get when I dragged the formula down and have to always fix manually).

I will try to implement your proposed solution (as I'm still not 100% sure I know what it entails) and will update the thread as needed.

Thanks for all the help, whether I can get it to work or not you'll be getting reputation just for the dedicated support.

**NBVC** · 06-03-2011, 03:29 PM

In F2, you just need to change the indexed range in the criteria to index the proper columns.

=DSUM(Sheet2!$A:$R,Sheet2!$J$1,INDEX(Sheet3!F:F,29+((ROW(A1)-1)*2)):INDEX(Sheet3!I:I,29+((ROW(A1)-1)*2)+1))