Select Short or Long Drop Down List in Excel

http://www.contextures.com/xlDataVal01.html In an Excel worksheet, add a drop down list in a cell, to make data entry easier.

In this example, created by Alex Jankowski, users can see a short list of frequently used customer names, or a long list, with all customer names.

No macros are required -- the data validation formula does all the work.

Watch this video to see how to name a list, then select the short or full drop down lists in the worksheet cells, based on that list.

These instructions are for Excel 2007 and Excel 2010. For Excel 2003, use the toolbar commands.

Category:

Science & Technology

Tags:

• Excel data validation
• Excel
• Excel drop down
• Excel drop down list
• data validation
• data validation list
• Excel data validation list
• Excel tips
• Contextures
• Alex Jankowski

[DJTamasH]

Thanks for this video it was very very usefull BUT I'd like to see some more information for the customer I have alredy choosen from the list on the same sheet.(like address,phone number etc)

Any link to show me this extra thing?

Cheers!!

[contextures]

@DJTamasH Thanks, and you can use a VLOOKUP formula in another cell, to show related information for the selected customer.

I can't add a link in this comment, but search in YouTube for Contextures VLOOKUP, then click on the video for "Find Product Price in Excel with VLOOKUP "

[ParSev21]

This is great! How do you do this for an entire column other than dragging the first cell down through the whole column?

[contextures]

@ParSev21 Instead of selecting just one cell (e.g. cell E3), select all the cells where you want the drop down lists, before you add the data validation.

Then, in the data validation formula, use a relative reference to the active cell, instead of an absolute reference.

For example, if the active cell is E2, refer to it as E2, instead of $E$2:

=IF(E2="Full List",FullList,ShortList)

[73davie]

really clever trick....thanks for sharing

[krn14242]

This is a great trick, thanks.

[aad11]

GREAT!!! :))

[dwheavyd]

Great idea! I hate scrolling through long lists, when 5 or 6 items are all I need 95% of the time. Thanks!