Home |
Search |
Today's Posts |
#1
|
|||
|
|||
variable as a cell reference?
i have this formula (and some others like it) that are basically summing
from the 24th row to the bottom row of a list of items, which in this case is row 203. =+SUMIF($A$24:$A$203,A5,$L$24:$L$203) i continually add items into the list so i am continually having to do 'insert row' into the spreadsheet so as to maintain the pointer to the last row, ie 203, which then becomes 204, 205...221. is it possible to make the $A$203 be $A$BottomRow where BottomRow can then be changed to whatever i want. i guess i would define a cell name to be BottomRow and then fill in whatever value i need. would avoid having to do all those inserts. Or perhaps more simply i could put a special character value lets say 'bottom' and have excel scan column A from top to 99999 looking for the word 'bottom' and then using that row to be the value of BottomRow. hope this is clear. thanks. jim |
#2
|
|||
|
|||
Why not just use a giant number:
=SUMIF($A$24:$A$65536,A5,$L$24:$L$65536) I used the maximum number of rows in a worksheet, but you could just make it bigger than anything you ever expect. (I also dropped that + from =+sumif(). It isn't required. jim sturtz wrote: i have this formula (and some others like it) that are basically summing from the 24th row to the bottom row of a list of items, which in this case is row 203. =+SUMIF($A$24:$A$203,A5,$L$24:$L$203) i continually add items into the list so i am continually having to do 'insert row' into the spreadsheet so as to maintain the pointer to the last row, ie 203, which then becomes 204, 205...221. is it possible to make the $A$203 be $A$BottomRow where BottomRow can then be changed to whatever i want. i guess i would define a cell name to be BottomRow and then fill in whatever value i need. would avoid having to do all those inserts. Or perhaps more simply i could put a special character value lets say 'bottom' and have excel scan column A from top to 99999 looking for the word 'bottom' and then using that row to be the value of BottomRow. hope this is clear. thanks. jim -- Dave Peterson |
#3
|
|||
|
|||
You could do
=SUMIF(INDIRECT("A24:A"&BottomRow),A5,INDIRECT("L2 0:L"&BottomRow)) or if there is nothing below, and you don't have lots of these formulae, you could just use =SUMIF($A$24:$A$65536,A5,$L$24:$L$65536) -- HTH RP (remove nothere from the email address if mailing direct) "jim sturtz" wrote in message ... i have this formula (and some others like it) that are basically summing from the 24th row to the bottom row of a list of items, which in this case is row 203. =+SUMIF($A$24:$A$203,A5,$L$24:$L$203) i continually add items into the list so i am continually having to do 'insert row' into the spreadsheet so as to maintain the pointer to the last row, ie 203, which then becomes 204, 205...221. is it possible to make the $A$203 be $A$BottomRow where BottomRow can then be changed to whatever i want. i guess i would define a cell name to be BottomRow and then fill in whatever value i need. would avoid having to do all those inserts. Or perhaps more simply i could put a special character value lets say 'bottom' and have excel scan column A from top to 99999 looking for the word 'bottom' and then using that row to be the value of BottomRow. hope this is clear. thanks. jim |
#4
|
|||
|
|||
"Bob Phillips" wrote...
You could do =SUMIF(INDIRECT("A24:A"&BottomRow),A5,INDIRECT("L 20:L"&BottomRow)) .... INDIRECT is volatile. INDEX isn't (most of the time). =SUMIF(A$24:INDEX(A:A,BottomRow),A5,L$24:INDEX(L:L ,BottomRow)) |
#5
|
|||
|
|||
i guess 1) to see if it could be done 2) aint elegant :) 3) i might put
something below so this seemed like a good solution thanks for all suggestions. jim "Dave Peterson" wrote in message ... Why not just use a giant number: =SUMIF($A$24:$A$65536,A5,$L$24:$L$65536) I used the maximum number of rows in a worksheet, but you could just make it bigger than anything you ever expect. (I also dropped that + from =+sumif(). It isn't required. jim sturtz wrote: i have this formula (and some others like it) that are basically summing from the 24th row to the bottom row of a list of items, which in this case is row 203. =+SUMIF($A$24:$A$203,A5,$L$24:$L$203) i continually add items into the list so i am continually having to do 'insert row' into the spreadsheet so as to maintain the pointer to the last row, ie 203, which then becomes 204, 205...221. is it possible to make the $A$203 be $A$BottomRow where BottomRow can then be changed to whatever i want. i guess i would define a cell name to be BottomRow and then fill in whatever value i need. would avoid having to do all those inserts. Or perhaps more simply i could put a special character value lets say 'bottom' and have excel scan column A from top to 99999 looking for the word 'bottom' and then using that row to be the value of BottomRow. hope this is clear. thanks. jim -- Dave Peterson |
#6
|
|||
|
|||
hmm. wasnt sure if we were supposed to be 'searching' for BottomRow to find
the 'marker' for it. or if i was supposed to define a variable called BottomRow and fill in the value myself. so i did both, singly, then together. i put BottomRow in a205 and i named a1 to be BottomRow and put a 205 in it. my prior formula went down to row 205, and the value totaled at that time $208.25 =+SUMIF($A$24:$A$205,A5,$L$24:$L$205) tried the function as =SUMIF($A$24:INDEX(A:A,BottomRow),A5,$L$24:INDEX(L :L,BottomRow)) and it adds up to $208.25, which is correct. im mystified, to determine what was picking up row 205 to be the end of everything i tried removing the BottomRow designator in A205. and cell h5 (which is the accumulator) still added to 208.25. so says i then the value of 205 i put in a1 is the determinant, so i changed it to 100. and it did change to 185.70, then i put in 0. the total went back to 208.25. i put in blank, still 208.25. anyway, the short story is if i put a value bigger than 24 into a1 and the row has the correct A5 value then it adds it in...proceeding down the column till we get to the last entry at 205, but if i put just nothing in a1, it sort of just adds up the whole column all the way to the bottom. if i jump down to row 233, put the right a5 value in and go out to L233 and put in an amount it adds that in too. so the value in a1(bottomrow) is some sort of limiter. if i only put 205, it sums to row205 (or anything less than that up to 24 at which point it treats anything less than 24 as 24 and gives me the value in row 24), if i put a blank or 0 in then it sums the whole column to end of worksheet i guess. if i remove the NAME bottomrow then it errors. jim "Harlan Grove" wrote in message ... "Bob Phillips" wrote... You could do =SUMIF(INDIRECT("A24:A"&BottomRow),A5,INDIRECT("L 20:L"&BottomRow)) ... INDIRECT is volatile. INDEX isn't (most of the time). =SUMIF(A$24:INDEX(A:A,BottomRow),A5,L$24:INDEX(L:L ,BottomRow)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change a cell range with a reference cell? | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Cell Reference Math | Excel Worksheet Functions | |||
Cell reference - for the sheet name, can I use a variable? | Excel Discussion (Misc queries) | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |