Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Picking up the last non-empty cell in a given range | Excel Discussion (Misc queries) | |||
How can I assign a range starting cell based on a variable locati. | Excel Worksheet Functions | |||
Refer to sheet name specified in other cell | Excel Discussion (Misc queries) | |||
Grabbing the last Non-empty cell in a range | Excel Discussion (Misc queries) | |||
Cell range in Excel | Excel Discussion (Misc queries) |