Excel Drop Down List with Data Validation and Auto Complete

Parth Shah's Blog

Have you ever ran into a scenario why you want to limit the possible values a cell can have but at the same time the range of values that cell can take is changing dynamically?

Scenario: You have a dedicated sheet for entering data into your Excel Workbook. One of the fields is best represented as a drop down list via default Excel Data Validation feature. You decide to use the default data validation and everything is working perfectly well until your drop down list grows to an unimaginable size. You now end up spending more time scrolling down that list than you do entering other information.

Real World Example: Company ABC uses Excel for entering Sales information. There is a dedicated worksheet called Record Sale in which the sales people are required to enter details such as Customer, Date, Amount, etc. Instead of allowing users to enter any random Customer name, Company ABC chose to…

View original post 471 more words