Excel - Automatic Drop Down List

Asked By aeddav on 12-Jan-08 01:08 PM
I have a workbook with 4 sheets.

Sheet 1 Column A allows users to enter a location name
Sheet 2 Column B automatically creates a dynamic drop-down of the Sheet 1
Column A data

How is this done?  I can't find any code, functions, formulas or anything
indicating how it is accomplished.

Thanks in advance.

JP replied on 13-Jan-08 03:10 AM
1. While on sheet 1, click Insert>Name>Define
2. In the "Names in workbook" box, type in a name for your range (ex:
3. In the "Refers To" box, paste in this formula:


4. Now on Sheet 2, highlight column B and goto Data>Validation:

Allow: List
Source: =3DLocations

Check the "In-cell dropdown" box if you like.
5. Click OK.


aeddav replied on 14-Jan-08 10:29 AM
Thanks JP.  This is is.  However, although it appears to work dynamically, it
does not show the bottom two "locations" from Sheet1ColA in the drop-down.
I'd appreciate any thoughts.
JP replied on 14-Jan-08 10:16 PM
Make sure there are no blank rows in Sheet 1, column A. The formula
assumes: number of entries =3D number of rows.


lly, it
t 1