Increment column on drag down

[mariette_hachey]

Greetings,
I have the following formula in cell B2 of a worksheet 4.
=SUM('Area Flooded per Property'!C:C,-'Area Flooded per Property'!C$1)
Area Flooded per property is sheet 2. I need to apply this formula down
the column to fill many rows, but i need the "C" to increment to "D"
and continue to increment. As follows

cell b3 =SUM('Area Flooded per Property'!D,-'Area Flooded per
Property'!D$1)
cell b4 =SUM('Area Flooded per Property'!E:E,-'Area Flooded per
Property'!E$1)
and so on and so forth

But when i drag this formula down there is no increment that occurs
even tho there is no $, does anybody know a way around this.
Thank You
Mariette

 

[SteveW]

Columns down't Drag down, but only across
By that I mean that the formula shouldn't automatically adjust

I'd *try* something like dragging it *across*, than transposing the cells
into a column.

Steve

 

[mariette_hachey]

This method does work, Thank You
But is it possible to do this as a formula or a macro?
Mariette

 

[Don Guillett]

try to change this to suit your needs

Sub coldown()
For i = 3 To 10
Cells(i-1, 2) = Application.Sum(Columns(i))
Next i
End Sub

 

[Roger Govier]

Hi Marriette

Try
=SUM(INDEX('Area Flooded per Property'!$A:$Z,,ROWS($1:3)))
-INDEX('Area Flooded per Property'!$A:$Z,1,ROWS($1:3))
and copy down

If you need to go beyond column Z, then alter the $A$Z to the range you
want
The 1 in the second part of the formula is telling it to deduct the
value in cell 1 of that column

If you wanted that cell value to increment as you go across the sheet,
to C2, C3 etc. then change the 1 to
COLUMNS($A:A)

 

[mariette_hachey]

Thank You Roger that worked perfectly.
Mariette

Hi Marriette

Try
=SUM(INDEX('Area Flooded per Property'!$A:$Z,,ROWS($1:3)))
-INDEX('Area Flooded per Property'!$A:$Z,1,ROWS($1:3))
and copy down

If you need to go beyond column Z, then alter the $A$Z to the range you
want
The 1 in the second part of the formula is telling it to deduct the
value in cell 1 of that column

If you wanted that cell value to increment as you go across the sheet,
to C2, C3 etc. then change the 1 to
COLUMNS($A:A)