|
Increment column on drag down
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: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 |
Increment column on drag down
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 On Thu, 30 Nov 2006 14:06:11 -0000, wrote: 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: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 |
Increment column on drag down
This method does work, Thank You
But is it possible to do this as a formula or a macro? Mariette SteveW wrote: 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 On Thu, 30 Nov 2006 14:06:11 -0000, wrote: 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: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 |
Increment column on drag down
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 -- Don Guillett SalesAid Software wrote in message ps.com... 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: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 |
Increment column on drag down
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) -- Regards Roger Govier wrote in message ups.com... This method does work, Thank You But is it possible to do this as a formula or a macro? Mariette SteveW wrote: 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 On Thu, 30 Nov 2006 14:06:11 -0000, wrote: 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: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 |
Increment column on drag down
Thank You Roger that worked perfectly.
Mariette Roger Govier wrote: 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) -- Regards Roger Govier wrote in message ups.com... This method does work, Thank You But is it possible to do this as a formula or a macro? Mariette SteveW wrote: 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 On Thu, 30 Nov 2006 14:06:11 -0000, wrote: 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: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 |
| All times are GMT +1. The time now is 04:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com