Excel Dropdown list – Excluding Previously Selected Items

Excel Dropdown list – Excluding Previously Selected Items
Last Updated on 11/09/2023
Reading time: 2 minutes

How do I create a dropdown list excluding the previously selected items?

  1. Create the first dropdown list

    Create a simple dropdown list without the specific option

  2. Write the formula excluding the previously selected items

    Here, we will use the COUNTIF and FILTER functions

  3. 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

Task Board with employee name

#1 Create a drop-down menu

In this first step, there is nothing complicated. You have added a dropdown list to the cell.

First DropDown List with all the name

#2 Formula excluding the previously selected item

To build this formula, we will use the FILTER and COUNTIF functions

Formula excluding previously selected items

You must write the arguments in the correct order in the COUNTIF function.

  1. First the cells that will contain the selections by dropdown menu
  2. Next, the initial list of employees

#3: Use this formula as the source of the new drop-down 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 to always select all the cells returned by the formula

=$B$2#

The source of the drowdown list use the spill ref of B2

And just like that, the second drop-down menu displays all the names except the previous selection, Michael

The first selected name is not in the second dropdown list

#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.

  1. Select cell E3 which contains the drop-down menu using the formula
  2. Copy cell
  3. Select cells E4 and E5
  4. Open the Paste Special dialog box (Ctrl + Alt + V)
  5. Select the Validation option
Paste Special Validation

And that's it 😀👍Each time you select a name, this name will not be visible for the other dropdown list

Dropdown list without listing the previous selected items

Leave a Reply

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

Excel Dropdown list – Excluding Previously Selected Items

Reading time: 2 minutes
Last Updated on 11/09/2023

How do I create a dropdown list excluding the previously selected items?

  1. Create the first dropdown list

    Create a simple dropdown list without the specific option

  2. Write the formula excluding the previously selected items

    Here, we will use the COUNTIF and FILTER functions

  3. 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

Task Board with employee name

#1 Create a drop-down menu

In this first step, there is nothing complicated. You have added a dropdown list to the cell.

First DropDown List with all the name

#2 Formula excluding the previously selected item

To build this formula, we will use the FILTER and COUNTIF functions

Formula excluding previously selected items

You must write the arguments in the correct order in the COUNTIF function.

  1. First the cells that will contain the selections by dropdown menu
  2. Next, the initial list of employees

#3: Use this formula as the source of the new drop-down 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 to always select all the cells returned by the formula

=$B$2#

The source of the drowdown list use the spill ref of B2

And just like that, the second drop-down menu displays all the names except the previous selection, Michael

The first selected name is not in the second dropdown list

#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.

  1. Select cell E3 which contains the drop-down menu using the formula
  2. Copy cell
  3. Select cells E4 and E5
  4. Open the Paste Special dialog box (Ctrl + Alt + V)
  5. Select the Validation option
Paste Special Validation

And that's it 😀👍Each time you select a name, this name will not be visible for the other dropdown list

Dropdown list without listing the previous selected items

Leave a Reply

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