extend the range of cells for a drop down list
 

I have a range of cells (named staff) on a worksheet in the workbook where
the drop down list will be used. Additional cells were added to the range of
cells, but I can't get Excel to include them on the named list. Can I not
expand the range of cells or do I need to name the expanded list with a new
range name?

Answer: extend the range of cells for a drop down list
 

How to expand the range of cells for a drop-down list in Excel

1. Select the cell with the drop-down list.
2. Go to the "Data" tab and click on "Data Validation".
3. In the "Data Validation" dialog box, go to the "Settings" tab.
4. Select "List" from the "Allow" drop-down menu.
5. In the "Source" field, you will see the range of cells named "staff".
6. Click on the "Collapse Dialog" button next to the "Source" field.
7. Select the additional cells you want to include in the drop-down list.
8. Click on the "Collapse Dialog" button again to return to the "Data Validation" dialog box.
9. The "Source" field will be updated with the new range of cells.
10. Click "OK" to save the changes.

No new range name is needed for the expanded list.

extend the range of cells for a drop down list
 

use a self adjusting defined name such as mylist and in the refers to box

=offset($a$1,1,0,counta($a:$a),1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marge" wrote in message
...
I have a range of cells (named staff) on a worksheet in the workbook where
the drop down list will be used. Additional cells were added to the range
of
cells, but I can't get Excel to include them on the named list. Can I not
expand the range of cells or do I need to name the expanded list with a
new
range name?

extend the range of cells for a drop down list
 

Marge
If you're using Excel 2007, simply delete the name "staff" and then name
the new list "staff". In earlier versions you can simply name the new list
"staff" without having to first delete that name. HTH Otto

"Marge" wrote in message
...
I have a range of cells (named staff) on a worksheet in the workbook where
the drop down list will be used. Additional cells were added to the range
of
cells, but I can't get Excel to include them on the named list. Can I not
expand the range of cells or do I need to name the expanded list with a
new
range name?

extend the range of cells for a drop down list
 

I'm working with Excel 2003. I tried selecting the expanded list and naming
it "staff"...it reverts back to the unexpanded list.

"Otto Moehrbach" wrote:

Marge
If you're using Excel 2007, simply delete the name "staff" and then name
the new list "staff". In earlier versions you can simply name the new list
"staff" without having to first delete that name. HTH Otto

"Marge" wrote in message
...
I have a range of cells (named staff) on a worksheet in the workbook where
the drop down list will be used. Additional cells were added to the range
of
cells, but I can't get Excel to include them on the named list. Can I not
expand the range of cells or do I need to name the expanded list with a
new
range name?

.

extend the range of cells for a drop down list
 

I don't know what a "refers to box" is...can you explain?

"Don Guillett" wrote:

use a self adjusting defined name such as mylist and in the refers to box

=offset($a$1,1,0,counta($a:$a),1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marge" wrote in message
...
I have a range of cells (named staff) on a worksheet in the workbook where
the drop down list will be used. Additional cells were added to the range
of
cells, but I can't get Excel to include them on the named list. Can I not
expand the range of cells or do I need to name the expanded list with a
new
range name?

.

extend the range of cells for a drop down list
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marge" wrote in message
...
I don't know what a "refers to box" is...can you explain?

"Don Guillett" wrote:

use a self adjusting defined name such as mylist and in the refers to box

=offset($a$1,1,0,counta($a:$a),1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marge" wrote in message
...
I have a range of cells (named staff) on a worksheet in the workbook
where
the drop down list will be used. Additional cells were added to the
range
of
cells, but I can't get Excel to include them on the named list. Can I
not
expand the range of cells or do I need to name the expanded list with a
new
range name?

.

extend the range of cells for a drop down list
 

Select "staff" in insertnamedefined names.

In the "refers to" dialog simply edit the range and OK

I like Don's suggestion of a dynamic range best however.


Gord Dibben MS Excel MVP

On Tue, 18 May 2010 10:48:10 -0700, Marge
wrote:

I'm working with Excel 2003. I tried selecting the expanded list and naming
it "staff"...it reverts back to the unexpanded list.

"Otto Moehrbach" wrote:

Marge
If you're using Excel 2007, simply delete the name "staff" and then name
the new list "staff". In earlier versions you can simply name the new list
"staff" without having to first delete that name. HTH Otto

"Marge" wrote in message
...
I have a range of cells (named staff) on a worksheet in the workbook where
the drop down list will be used. Additional cells were added to the range
of
cells, but I can't get Excel to include them on the named list. Can I not
expand the range of cells or do I need to name the expanded list with a
new
range name?

.

extend the range of cells for a drop down list
 

Marge
In 2003, when you do Insert - Name - Define, if you select the name, it
will revert back to the old list. Don't select the name. Type it in. HTH
Otto

"Marge" wrote in message
...
I'm working with Excel 2003. I tried selecting the expanded list and
naming
it "staff"...it reverts back to the unexpanded list.

"Otto Moehrbach" wrote:

Marge
If you're using Excel 2007, simply delete the name "staff" and then
name
the new list "staff". In earlier versions you can simply name the new
list
"staff" without having to first delete that name. HTH Otto

"Marge" wrote in message
...
I have a range of cells (named staff) on a worksheet in the workbook
where
the drop down list will be used. Additional cells were added to the
range
of
cells, but I can't get Excel to include them on the named list. Can I
not
expand the range of cells or do I need to name the expanded list with a
new
range name?

.

extend the range of cells for a drop down list
 

Hi,

Simply convert the range to a Table (Ctrl+L). When you convert a range to a
Table, it auto expands

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Marge" wrote in message
...
I have a range of cells (named staff) on a worksheet in the workbook where
the drop down list will be used. Additional cells were added to the range
of
cells, but I can't get Excel to include them on the named list. Can I not
expand the range of cells or do I need to name the expanded list with a
new
range name?