Hide Used Items in Drop Down List

Don’t let Excel yell at us, if we accidentally enter a duplicate value in a column. Okay, maybe Excel isn’t 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 can’t pick an item if it isn’t there!

Hide Used Items

With data validation, it’s 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.

datavalcombosheet01

Allow Multiple Uses

Sometimes duplicates are okay – if you sell Pens, it’s okay to add that product to any row where it was sold.

dropdownlistblank05

Use Items Once

But sometimes you only want an item used once. For example, if you’re 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 doesn’t show up in the list now. Somebody else will have to be the catcher.

datavalhidden01

Or, if you’re 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 aren’t in the list now.

datavalhidden04

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.

datavalhidden07

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.

datavalhidden08

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.

datavalhidden02

Get the Workbooks

You can get the detailed instructions, and sample workbooks, on the Hide the Used Items page on my Contextures website.

There are four example files to download, and no macros in the workbooks.

_________________

Save

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *