If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Expanding a named range
I Have a set of values in a column that keeps track of customers for a given
product. I have a CountA function set up to count the non-null values in the range, thus keeping track of how many customers I have for a given product. My problem is this: When I create a new row in the table to insert a new customer, that entry now falls a row outside of my defined, named range, and the new entry doesn't get counted in my COUNTA. Is there a way to redefine (stretch) the range I have defined as "customers" so that it automatically grows row by row each time I ADD insert a new row? |
#2
|
|||
|
|||
Expanding a named range
Hi,
1. You can insert a row within the range 2. You can define your range to include 1 extra row and then insert a new row at that point each time. 3. You can use a dynamic range name - =OFFSET(A$1,0,0,COUNTA(A$1:A$1000)) Enter the above in the Refers to box of the Define Names area. You may need to adjust to compensate for titles or blank rows at the top. If this helps, please click the Yes button -- Thanks, Shane Devenshire "jayceejay" wrote: I Have a set of values in a column that keeps track of customers for a given product. I have a CountA function set up to count the non-null values in the range, thus keeping track of how many customers I have for a given product. My problem is this: When I create a new row in the table to insert a new customer, that entry now falls a row outside of my defined, named range, and the new entry doesn't get counted in my COUNTA. Is there a way to redefine (stretch) the range I have defined as "customers" so that it automatically grows row by row each time I ADD insert a new row? |
#3
|
|||
|
|||
Expanding a named range
Hi,
You can also try the following: Highlight the range and convert it to a list - Data List Create List -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jayceejay" wrote in message ... I Have a set of values in a column that keeps track of customers for a given product. I have a CountA function set up to count the non-null values in the range, thus keeping track of how many customers I have for a given product. My problem is this: When I create a new row in the table to insert a new customer, that entry now falls a row outside of my defined, named range, and the new entry doesn't get counted in my COUNTA. Is there a way to redefine (stretch) the range I have defined as "customers" so that it automatically grows row by row each time I ADD insert a new row? |
#4
|
|||
|
|||
Expanding a named range
Thanks to both of you. I shall try all of these options to weigh the best solution! Regards! "Ashish Mathur" wrote: Hi, You can also try the following: Highlight the range and convert it to a list - Data List Create List -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jayceejay" wrote in message ... I Have a set of values in a column that keeps track of customers for a given product. I have a CountA function set up to count the non-null values in the range, thus keeping track of how many customers I have for a given product. My problem is this: When I create a new row in the table to insert a new customer, that entry now falls a row outside of my defined, named range, and the new entry doesn't get counted in my COUNTA. Is there a way to redefine (stretch) the range I have defined as "customers" so that it automatically grows row by row each time I ADD insert a new row? |
Thread Tools | |
Display Modes | |
|
|