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

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?

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

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

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

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?