Delete the list name from a cell range
 

I have name a cell range say from C2:K5 as NameLookup.
Now I added some rows to it so the range should be C2:K10 and want to name
the same name.
How do I do that?

Delete the list name from a cell range
 

define the name
in the refers to box
=offset($C$2,0,0,counta(c:c)-1,9)
then it will be self-adjusting. Change c:c to the longest in the range

--
Don Guillett
SalesAid Software

"Man Utd" wrote in message
...
I have name a cell range say from C2:K5 as NameLookup.
Now I added some rows to it so the range should be C2:K10 and want to name
the same name.
How do I do that?

Delete the list name from a cell range
 

Dave,

As the formula removes C1 from the count, why C1 must has something ?
And what is the '9' does in the formula ?

As I think this formula can replace the name of the cell range used in the
Dta|Validation for a dropdownbox. My problem is I need another dropdownbox
as detail to this first dropdownbox.

If you add the entries in column C and don't leave empty cells, it should
work
ok.

You can test it by adding/deleting entries from column C.

Then Edit|Goto|
type in the name you used and see what gets selected.

Although I'd use a slightly different formula:

=OFFSET($C$2,0,0,COUNTA($C:$C)-1,9)

And make sure C1 has something in it. (The -1 removes C1 from the count
of
cells used in column C.)

Delete the list name from a cell range
 

In addition, how do I refer this cell range from another worksheet ?

"Man Utd" wrote in message
...
Dave,

As the formula removes C1 from the count, why C1 must has something ?
And what is the '9' does in the formula ?

As I think this formula can replace the name of the cell range used in the
Dta|Validation for a dropdownbox. My problem is I need another dropdownbox
as detail to this first dropdownbox.

If you add the entries in column C and don't leave empty cells, it
should
work
ok.

You can test it by adding/deleting entries from column C.

Then Edit|Goto|
type in the name you used and see what gets selected.

Although I'd use a slightly different formula:

=OFFSET($C$2,0,0,COUNTA($C:$C)-1,9)

And make sure C1 has something in it. (The -1 removes C1 from the count
of
cells used in column C.)

Delete the list name from a cell range
 

If you have something in C1:C10, then you have 10 items.
If you something in C2:C10 (with C1 empty), then you only have 9 entries.

Bob's formula subtracts one from the total. So if C1 is empty, it will evaluate
to 8 -- instead of 9.

If you want C1 to be empty, you can remove that -1 from the formula.

=OFFSET($C$2,0,0,COUNTA($C:$C)-1,9)

The formula says to start at C2 (and stay there with the 0,0).

Then it says to go down counta($c:$c)-1 rows.
and then go over 9 columns.

But if you're going to use that name as in the list for Data|Validation, you'll
only want one column.

Debra Dalgleish has some nicer instructions (with pictures) at:
http://www.contextures.com/xlNames01.html#Dynamic

And if you want to use dependent lists with Data|Validation, take a look at this
portion of Debra's site:

http://www.contextures.com/xlDataVal02.html



Man Utd wrote:

Dave,

As the formula removes C1 from the count, why C1 must has something ?
And what is the '9' does in the formula ?

As I think this formula can replace the name of the cell range used in the
Dta|Validation for a dropdownbox. My problem is I need another dropdownbox
as detail to this first dropdownbox.

If you add the entries in column C and don't leave empty cells, it should
work
ok.

You can test it by adding/deleting entries from column C.

Then Edit|Goto|
type in the name you used and see what gets selected.

Although I'd use a slightly different formula:

=OFFSET($C$2,0,0,COUNTA($C:$C)-1,9)

And make sure C1 has something in it. (The -1 removes C1 from the count
of
cells used in column C.)

--

Dave Peterson

Delete the list name from a cell range
 

See Debra Dalgleish's site:
http://www.contextures.com/xlDataVal01.html#Name

Man Utd wrote:

In addition, how do I refer this cell range from another worksheet ?

"Man Utd" wrote in message
...
Dave,

As the formula removes C1 from the count, why C1 must has something ?
And what is the '9' does in the formula ?

As I think this formula can replace the name of the cell range used in the
Dta|Validation for a dropdownbox. My problem is I need another dropdownbox
as detail to this first dropdownbox.

If you add the entries in column C and don't leave empty cells, it
should
work
ok.

You can test it by adding/deleting entries from column C.

Then Edit|Goto|
type in the name you used and see what gets selected.

Although I'd use a slightly different formula:

=OFFSET($C$2,0,0,COUNTA($C:$C)-1,9)

And make sure C1 has something in it. (The -1 removes C1 from the count
of
cells used in column C.)



--

Dave Peterson