Dont let Excel yell at us, if we accidentally enter a duplicate value in a column. Okay, maybe Excel isnt really yelling, but it seems like that when a data validation error message pops onto the screen. Instead of letting people pick a duplicate, use this trick to hide used items in a worksheet drop down list. You cant pick an item if it isnt there!
With data validation, its easy to make a drop down list of names, or weekdays, or products, or whatever else you need. Then, instead of typing, just pick from the list.
Allow Multiple Uses
Sometimes duplicates are okay if you sell Pens, its okay to add that product to any row where it was sold.
Use Items Once
But sometimes you only want an item used once. For example, if youre coaching a baseball team, each player is assigned to one position at the start of the inning.
In the drop down list shown below, Mike was assigned as the pitcher, so his name doesnt show up in the list now. Somebody else will have to be the catcher.
Or, if youre creating a work schedule, employees should only be scheduled once per day.
In the next drop down list, Fred and Joe have been assigned, so their names arent in the list now.
Formulas Remove Used Items
To hide the used items, some fancy formulas are used on another worksheet.
In column B, names that have a number have not been used.
Then, in column C, an array formula sorts the names by those numbers, so that moves blank cells to the end of the list.
The names in column C are used for the drop down list.
Dependent Drop Downs
You can use this technique with dependent drop down lists too. In the example shown below, choose a type in column A.
Then, choose a related item in column B, but if an item has been used, like SI-02, it is removed from the drop down list.
Get the Workbook
You can get the detailed instructions, and sample workbook, on the Hide Used Items page on my Contextures website.
There are four example files to download, and no macros in the workbooks.
_________________