Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Sumif formula parameters for added data
2007 (compatibility mode 2003)
Two sections of w/s Top contains Sumif formulas Bottom contains detail data which is Sumif'd in the top section Data has been added to the bottom section. All of the Sumif formulas contain (except for the Relative "A2"): =SUMIF($A$68:$A$350,A2,$D$68:$D$350) The new data range extends to 386 Therefore, the formula needs to be =SUMIF($A$68:$A$386,A2,$D$68:$D$386) I could use a Replace but I do not think that it is the smartest approach. Plus I would have to calculate the number to replace. I have a variable in the code area for the Row of the last data item. Thoughts? TIA EagleOne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Sumif formula parameters for added data
Hi
you could set up some dynamic ranges. You say you have a variable which gives the last row number. Lets say this is held in cell A1 InsertNameDefine Name rngA Refers to =$A$68:INDEX($A:$A,$A$1) Name rngD Refers to =$D$68:INDEX($D:$D,$A$1) then =SUMIF(rngA,A2,rngD) -- Regards Roger Govier wrote in message ... 2007 (compatibility mode 2003) Two sections of w/s Top contains Sumif formulas Bottom contains detail data which is Sumif'd in the top section Data has been added to the bottom section. All of the Sumif formulas contain (except for the Relative "A2"): =SUMIF($A$68:$A$350,A2,$D$68:$D$350) The new data range extends to 386 Therefore, the formula needs to be =SUMIF($A$68:$A$386,A2,$D$68:$D$386) I could use a Replace but I do not think that it is the smartest approach. Plus I would have to calculate the number to replace. I have a variable in the code area for the Row of the last data item. Thoughts? TIA EagleOne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Sumif formula parameters for added data
Why not juts use a range bigger than you would ever need?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... 2007 (compatibility mode 2003) Two sections of w/s Top contains Sumif formulas Bottom contains detail data which is Sumif'd in the top section Data has been added to the bottom section. All of the Sumif formulas contain (except for the Relative "A2"): =SUMIF($A$68:$A$350,A2,$D$68:$D$350) The new data range extends to 386 Therefore, the formula needs to be =SUMIF($A$68:$A$386,A2,$D$68:$D$386) I could use a Replace but I do not think that it is the smartest approach. Plus I would have to calculate the number to replace. I have a variable in the code area for the Row of the last data item. Thoughts? TIA EagleOne |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Sumif formula parameters for added data
Hi Bob!
Does that not use more memory and/or computation? Also, the App may well have rather large possible data items. "Bob Phillips" wrote: Why not juts use a range bigger than you would ever need? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Sumif formula parameters for added data
Sumif and countif only look at the UsedRange.
so =Sumif(A:A,1,B:B) would take no more resources than =Sumif(A1:A10,1,B1:B10) if the used range ends at row 10. That isn't true for most other functions. So using $A$68:$A$2000 shouldn't be a problem. -- Regards, Tom Ogilvy " wrote: Hi Bob! Does that not use more memory and/or computation? Also, the App may well have rather large possible data items. "Bob Phillips" wrote: Why not juts use a range bigger than you would ever need? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Sumif formula parameters for added data
Roger, I like your idea - use of Index().
Index is an XL function. What is the comparable VBA function? "Roger Govier" wrote: Hi you could set up some dynamic ranges. You say you have a variable which gives the last row number. Lets say this is held in cell A1 InsertNameDefine Name rngA Refers to =$A$68:INDEX($A:$A,$A$1) Name rngD Refers to =$D$68:INDEX($D:$D,$A$1) then =SUMIF(rngA,A2,rngD) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leaving a formula cell blank when data has not yet been added. | Excel Discussion (Misc queries) | |||
Formula needed: argument changes as more data is added | Excel Worksheet Functions | |||
Array in formula Vlookup changes when data list is added to | Excel Worksheet Functions | |||
Parameters of SUMIF in VBA | Excel Programming | |||
When I click buttons I have added the font or button resize, Why? | Excel Discussion (Misc queries) |