How can I make a range "dynamic"?

[Conan Kelly]

Hello all,

I have a range of cells that I need to reference. This range always
starts at B23 and goes to B??? (the last row will be different each
time we open the file and load data). What I want to be able to do is
enter the number of the last row of this range in B1 and then
reference from that cell. Let's say this time I'm using the file, the
last row will be 987, so my range I need to reference will need to be
B23:B987. I want to enter 987 in B1 and then something like:

=SUM(B23:B(& the value in B1))

Any help will be greatly appreciated,

Conan Kelly

 

[Ken Wright]

Anything wrong with using =SUM(B:B)

Failing that, do you have any gaps at all in the data from B23 to B?? and
is there any data in column B below B??

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Aladin Akyurek]

B1:

=MATCH(9.99999999999999E+307,B23:B65536)

=SUM(B23:OFFSET(B23,B1-1,0))

 

[Biff]

Another option:

=SUM(B23:INDEX(B23:B65536,B1))

Or, eliminate the use of the formula in B1:

=SUM(B23:INDEX(B23:B65536,MATCH(9.9999999999999E+307,B23:B65536)))

Biff

 

[Robert_Steel]

Another option
Don't worry about using B1 and
=Sum(B:B)-Sum(B1:B22)

It elimnates the '9.9999999999999E+307' that I know Biff likes so much
<bg>

Cheers RES

 

[Biff]

It elimnates the '9.9999999999999E+307' that I know Biff likes so much

<bg>

argh!

<vbg>

Biff

 

[Guest]

argh!

<vbg>

Biff