with Dynamic Formulas

Using Dynamic Array Formulas

Create the tables

This example shows you how to prevent a user from selecting the same value more than once from a drop-down list.
Once the entry has been selected the item is automatically removed from the other drop-down lists.
Create the following table. We want the drop-down lists to appear in cells "C3:C9".

Create a table called "tblDays" for the cell range "B2:C9".

Create a list of all the entries that we want to display in our drop-down list.
Enter this information into column "E".
Create a table called "tblFullList" for the cell range "E2:E9".

Use Array Formula

Create a placeholder for a list of the available names.
This list will be dynamically populated with the list of entries that have not yet been chosen.
Enter this information into column "G"
This column will be populated with a dynamic array formula that will spill over to the necessary cells below.
Enter the following formula into cell "G3".
=SORT(FILTER(tblFullList[Names], COUNTIF(tblDays[Choice], tblFullList[Names])=0))
The SORT function returns the array of data in a range that has been sorted.
The FILTER function returns the array of data in a range that satisfies multiple conditions.
The COUNTIF function returns the number of non blank cells that satisfies one condition.

Create Dynamic Named Range

The number of entries in this column will change so we need to create a Dynamic Named Range.
Display the Formulas tab and find the Defined Names group.
Press Named Manager to display the "Name Manager" dialog box.
Enter the following into the Refers To box:
=OFFSET(Sheet1!$D$20,0,0, COUNT(Sheet1!$C$20:$C$23),1)
Press New and create a workbook level named range called "Available".

Enter the Criteria

Select the cells "C3:C9".
Display the Data tab and find the Data Tools group.
Press Data Validation to display the Data Validation dialog box and select the Settings tab.
Select "List" from the Allow drop-down list.
Enter the formula "=$G$3#" in the Source text box.

Press OK to apply the data validation.