Drop down list to allow only unique values in a column

**Mzun** · 06-21-2013, 10:43 AM

I have a list of items on column A as follows:

Monday
Tuesday
Wednesday
Thursday
Friday

In column B rows 1 to 5, I have drop down lists which can select values from column A. I would like to set it up so that if Monday was selected in cell B1 it would not be available for selection in any of the other cells in column B. How do I do this?

**wfm007** · 06-21-2013, 11:07 AM

In data validation custom option put this formula

Please Login or Register  to view this content.

**tigeravatar** · 06-21-2013, 11:21 AM

Mzun,

Welcome to the forum!
Attached is an example workbook based on the criteria you described.
Row 1 is a header row so actual data starts on row 2
Column A contains the full list of Days
Column B contains the list of Remaining Days (those that have not yet been selected). It gets those days with this formula in cell B2 and copied down:

Please Login or Register  to view this content.

Then I created a named range for the Remaining Days named listRemainingDays which is defined with this formula:

Please Login or Register  to view this content.

In column D is the drop-down list where you can select a day. Each time you select a day, the other drop-down lists will have had the selected days removed from their lists. They use the Data Validation List option with formula:

Please Login or Register  to view this content.

It should be noted that wfm007's proposed solution will also prevent duplicates from being entered, but it does not allow a drop-down list. The days would have to be manually typed in, which can allow for spelling errors.