How do I refer a Range to a Cell
 

I have a range of cells that is ever increasing.
I also have a number of formula that refer to the range.
To save changing the formula every time the range increases in size, I was
wanting to have a single reference cell that I could change to reference the
extent of the range.
For example:-
Range b1:B5
size of the range increases to B1:b10
I want to put in cell A1, a reference "10" that I can increase as the range
increases and without having to change the separate formula that refer to
the range.
So the forhula would be something like =B1:B(value(a1).
Any help appreciated.
Thanks,
Mal

For example:

=SUM(INDIRECT("B1:B"&A1))

--

Vasant

"Mal" wrote in message
...
I have a range of cells that is ever increasing.
I also have a number of formula that refer to the range.
To save changing the formula every time the range increases in size, I was
wanting to have a single reference cell that I could change to reference
the
extent of the range.
For example:-
Range b1:B5
size of the range increases to B1:b10
I want to put in cell A1, a reference "10" that I can increase as the
range
increases and without having to change the separate formula that refer to
the range.
So the forhula would be something like =B1:B(value(a1).
Any help appreciated.
Thanks,
Mal

Hi!

Instead of doing it that way, just create a dynamic named range.
Instructions are he

http://contextures.com/xlNames01.html#Dynamic

Biff

"Mal" wrote in message
...
I have a range of cells that is ever increasing.
I also have a number of formula that refer to the range.
To save changing the formula every time the range increases in size, I was
wanting to have a single reference cell that I could change to reference
the extent of the range.
For example:-
Range b1:B5
size of the range increases to B1:b10
I want to put in cell A1, a reference "10" that I can increase as the
range increases and without having to change the separate formula that
refer to the range.
So the forhula would be something like =B1:B(value(a1).
Any help appreciated.
Thanks,
Mal

i'm sure getting tired of explorer telling me "we're sorry....unable to
service request."

you could also set up a dynamic named range if you go to
Insert/Names/Define, enter a name for your table and the following formula.
This will define a table for a column of numbers in column B of Sheet1 (as
long as that's the only data in column B).

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!B:B),1)

"Mal" wrote:

I have a range of cells that is ever increasing.
I also have a number of formula that refer to the range.
To save changing the formula every time the range increases in size, I was
wanting to have a single reference cell that I could change to reference the
extent of the range.
For example:-
Range b1:B5
size of the range increases to B1:b10
I want to put in cell A1, a reference "10" that I can increase as the range
increases and without having to change the separate formula that refer to
the range.
So the forhula would be something like =B1:B(value(a1).
Any help appreciated.
Thanks,
Mal

Hi!

i'm sure getting tired of explorer telling me "we're sorry....unable to
service request."

That's the "new and improved" CDO!

I used to like the "old" version but this new one is totally laughable. What
a horrible design!

Biff

"JMB" wrote in message
...
i'm sure getting tired of explorer telling me "we're sorry....unable to
service request."

you could also set up a dynamic named range if you go to
Insert/Names/Define, enter a name for your table and the following
formula.
This will define a table for a column of numbers in column B of Sheet1 (as
long as that's the only data in column B).

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!B:B),1)

"Mal" wrote:

I have a range of cells that is ever increasing.
I also have a number of formula that refer to the range.
To save changing the formula every time the range increases in size, I
was
wanting to have a single reference cell that I could change to reference
the
extent of the range.
For example:-
Range b1:B5
size of the range increases to B1:b10
I want to put in cell A1, a reference "10" that I can increase as the
range
increases and without having to change the separate formula that refer to
the range.
So the forhula would be something like =B1:B(value(a1).
Any help appreciated.
Thanks,
Mal

if nothing else, it's predictable. I hate typing in a response, clicking
post, then losing the whole thing and having to start over. sorry for
getting off topic.

"Biff" wrote:

Hi!

i'm sure getting tired of explorer telling me "we're sorry....unable to
service request."

That's the "new and improved" CDO!

I used to like the "old" version but this new one is totally laughable. What
a horrible design!

Biff

"JMB" wrote in message
...
i'm sure getting tired of explorer telling me "we're sorry....unable to
service request."

you could also set up a dynamic named range if you go to
Insert/Names/Define, enter a name for your table and the following
formula.
This will define a table for a column of numbers in column B of Sheet1 (as
long as that's the only data in column B).

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!B:B),1)

"Mal" wrote:

I have a range of cells that is ever increasing.
I also have a number of formula that refer to the range.
To save changing the formula every time the range increases in size, I
was
wanting to have a single reference cell that I could change to reference
the
extent of the range.
For example:-
Range b1:B5
size of the range increases to B1:b10
I want to put in cell A1, a reference "10" that I can increase as the
range
increases and without having to change the separate formula that refer to
the range.
So the forhula would be something like =B1:B(value(a1).
Any help appreciated.
Thanks,
Mal

Thanks people. Problem solved.
Mal

"Mal" wrote in message
...
I have a range of cells that is ever increasing.
I also have a number of formula that refer to the range.
To save changing the formula every time the range increases in size, I was
wanting to have a single reference cell that I could change to reference
the extent of the range.
For example:-
Range b1:B5
size of the range increases to B1:b10
I want to put in cell A1, a reference "10" that I can increase as the
range increases and without having to change the separate formula that
refer to the range.
So the forhula would be something like =B1:B(value(a1).
Any help appreciated.
Thanks,
Mal