| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
| match and count words | Excel Worksheet Functions | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Linear Mode
