How to exclude items from a dropdown list to avoid duplicate selection?
- Create the first dropdown list
Create a simple dropdown list without the specific option
- Write the formula excluding the previously selected items
Here, we will use the COUNTIF and FILTER functions
- Create the other dropdown lists
Here, the source must be the formula
Task board for the employees
In the following document, we have the list of employees with tasks to perform. The team leader wants to build a series of dropdown lists to assign a task to one of the employees. But, the dropdown list should not display the names previously selected
#1 Create a drop-down menu
In this first step, there is nothing complicated. You have added a dropdown list to the cell.
#2 Formula excluding the previously selected item
To build this formula, we will use the FILTER and COUNTIF functions
You must write the arguments in the correct order in the COUNTIF function.
- First, the cells that will contain the selections by dropdown menu
- Next, the initial list of employees
#3: Use this formula as the source of the new dropdown menus
The previous formula is a dynamic array. That is, the result will be displayed in several cells. Therefore, we must use the symbol # to make sure always to select all the cells returned by the formula
=$B$2#
And just like that, the second drop-down menu displays all the names except the previous selection, Michael
#4: Copy and paste only drop-down menus
In Excel, you can copy only the drop-down menus of a cell by using the paste-special technique.
- Select cell E3 which contains the drop-down menu using the formula
- Copy cell
- Select cells E4 and E5
- Open the Paste Special dialog box (Ctrl + Alt + V)
- Select the Validation option
And that's it 😀👍Each time you select a name, the item will be excluded from the other dropdown list.
