Excel validation of a huge list

py1ro

Board Regular
Joined
Sep 30, 2005
Messages
70
In a Data Entry application I´d like to validate a cell where I choose the name of one of the companie's 1000+ truck drivers. With such a large list, using the standard validation function (Data>validation, etc) is just not practical. Does anyone have a suggestion of how to do this more "elegantly" ?

Rolf in Rio de Janeiro, Brazil
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Rolf,

If you want to validate such a large list, you will most likely need to let your users type in the data and validate using a formula.

Example: you want to validate that input in B3 matches anything in a list called Items.

Data > Validation
From the options, select Custom
The entry bar at the bottom is now Formula.
Insert this:
=COUNTIF(Items,B3)>0

OK to close and you're done. I tested this on a list with just under 4K items.

Denis
 

py1ro

Board Regular
Joined
Sep 30, 2005
Messages
70
Hi Denis and thanks for the suggestion which sounded like what I needed, however it brings up another problem:

The names to be entered come written on hardcopy documents in various forms like "John Doe", "J. Doe", "J Doe" etc. In other words, If the operator doesn´t remember how exactly John Doe is registered in the name-list, it will be a problem.

The ideal solution would be: The operator starts typing a name. With the first letter typed, a window opens with all the names starting with that letter. As typing progresses, the number of options will reduce. At any point the operator can click on the correct name to be entered.

This feature exists in some other software but I don´t think it is available in Excel.

Maybe I´ll just have to face the facts and write a macro to do just that!

Thanks again!
Rolf
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top