Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range
I need to add up numbers in a column, my column will have a dynamic height though. The range now for instance is B3-B12, next month it could be B3-B23 and so on. The very bottom field in that column will sum up the above values but again that field will have to move down as the column grows Siggestions? -- pelachrum ------------------------------------------------------------------------ pelachrum's Profile: http://www.excelforum.com/member.php...o&userid=35962 View this thread: http://www.excelforum.com/showthread...hreadid=557594 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range
Create a Dynamic Range.
InsertNameDefine Copy the formula below and paste into the "refers to:" dialog box. =OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B:$B),1) Adjust Sheet1 to your sheetname Give it a name like dyno and OK In a cell enter =SUM(dyno) Gord Dibben MS Excel MVP On Sat, 1 Jul 2006 12:58:23 -0500, pelachrum wrote: I need to add up numbers in a column, my column will have a dynamic height though. The range now for instance is B3-B12, next month it could be B3-B23 and so on. The very bottom field in that column will sum up the above values but again that field will have to move down as the column grows Siggestions? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range
How about just using a row number that's bigger than you'll ever need:
=sum(b3:b9999) or even =sub(b3:b65536) And if you don't have numbers in B1:B2, you can just use: =sum(b:b) or if you have numbers: =sum(b:b)-sum(b1:b2) pelachrum wrote: I need to add up numbers in a column, my column will have a dynamic height though. The range now for instance is B3-B12, next month it could be B3-B23 and so on. The very bottom field in that column will sum up the above values but again that field will have to move down as the column grows Siggestions? -- pelachrum ------------------------------------------------------------------------ pelachrum's Profile: http://www.excelforum.com/member.php...o&userid=35962 View this thread: http://www.excelforum.com/showthread...hreadid=557594 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range
thank you both -- pelachrum ------------------------------------------------------------------------ pelachrum's Profile: http://www.excelforum.com/member.php...o&userid=35962 View this thread: http://www.excelforum.com/showthread...hreadid=557594 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range
actually one followup question... is there a way to make the "=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B:$B), 1)" formula work when the field that calculates the rest actually sits in the same column, on top, in field B1 to be specific? -- pelachrum ------------------------------------------------------------------------ pelachrum's Profile: http://www.excelforum.com/member.php...o&userid=35962 View this thread: http://www.excelforum.com/showthread...hreadid=557594 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range
Change the refers to range
=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B2:$B10000) ,1) If you're going to do that, you may as well go with Dave P's suggestion of =SUM($B$3:$B$10000) or some row number greater than you think will ever be used. Gord Dibben MS Excel MVP On Sun, 2 Jul 2006 15:05:00 -0500, pelachrum wrote: actually one followup question... is there a way to make the "=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B:$B),1 )" formula work when the field that calculates the rest actually sits in the same column, on top, in field B1 to be specific? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drawing data from specific columns of a dynamic range | Setting up and Configuration of Excel | |||
Crate group of date, with Dynamic Range in pivot table not working | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions | |||
Reference to a dynamic range | Excel Discussion (Misc queries) |