Automatic Drop Down List

  • Thread starter Thread starter aeddave
  • Start date Start date
A

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.
 
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
 
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.
 
Make sure there are no blank rows in Sheet 1, column A. The formula
assumes: number of entries = number of rows.

HTH,
JP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dependent drop down dynamic range formula, off sheet. 7
Drop Down with dynamic named range offsheet source list 14
Copy corresponding cell in Column B if cell in A contains certain text? 1
Using drop-down lists 4
Drop down list question 2
Formatted cell column in drop down list 1
SUMIF or SUMIFS help 1
Drop Down Lists 2007 10

Back
Top