A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

extend the range of cells for a drop down list



 
 
Thread Tools Display Modes
  #1  
Old May 18th 10, 05:03 PM posted to microsoft.public.excel.worksheet.functions
Marge
external usenet poster
 
Posts: 33
Default 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?
Ads
  #2  
Old May 18th 10, 05:13 PM posted to microsoft.public.excel.worksheet.functions
Don Guillett[_2_]
external usenet poster
 
Posts: 1,517
Default 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?


  #3  
Old May 18th 10, 05:18 PM posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach[_2_]
external usenet poster
 
Posts: 1,071
Default 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?


  #4  
Old May 18th 10, 06:48 PM posted to microsoft.public.excel.worksheet.functions
Marge
external usenet poster
 
Posts: 33
Default 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?

>
> .
>

  #5  
Old May 18th 10, 06:50 PM posted to microsoft.public.excel.worksheet.functions
Marge
external usenet poster
 
Posts: 33
Default 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?

>
> .
>

  #7  
Old May 18th 10, 08:12 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,911
Default extend the range of cells for a drop down list

Select "staff" in insert>name>defined 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?

>>
>> .
>>


  #8  
Old May 18th 10, 09:42 PM posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach[_2_]
external usenet poster
 
Posts: 1,071
Default 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?

>>
>> .
>>

  #9  
Old May 19th 10, 12:49 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,766
Default 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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create a drop-down list from a range of cells weswhite7 Excel Worksheet Functions 2 July 8th 09 09:50 PM
Protect Drop-down list from range of cells JF Excel Worksheet Functions 3 April 1st 08 07:58 PM
how can I extend my drop-down box length to include all the list? sigeco Excel Discussion (Misc queries) 4 June 20th 06 03:35 AM
"Create a drop-down list from a range of cells" DILNAVAS Excel Worksheet Functions 1 May 3rd 06 08:40 AM
How to extend the no' of displayed entries in a drop down list SlowDriver Excel Discussion (Misc queries) 0 March 1st 06 10:51 AM


All times are GMT +1. The time now is 01:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.