Automatic Drop Down List

[aeddave]

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]

1. While on sheet 1, click Insert>Name>Define
2. In the "Names in workbook" box, type in a name for your range (ex:
"Locations")
3. In the "Refers To" box, paste in this formula:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

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

Allow: List
Source: =Locations

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


HTH,
JP

 

[aeddave]

Thanks JP. This is is. However, although it appears to work dynamically, it
doesn't show the bottom two "locations" from Sheet1ColA in the drop-down.
I'd appreciate any thoughts.

 

[JP]

Make sure there are no blank rows in Sheet 1, column A. The formula
assumes: number of entries = number of rows.

HTH,
JP