You can create multiple drop-down lists in Excel using the same source data. However, sometimes, it is needed to make the selection exclusive (such that once selected, the option should not appear in other drop-down lists). For example, this could be the case when you are assigning meeting roles to people (where one person takes one role only).
Creating Multiple Drop-down Lists in Excel without Repetition
In this blog post, learn how to create multiple drop-down Lists in Excel, where there is no repetition. Something as shown below:
To create this, we need to create a dynamic named range that would update automatically to remove a name if it has already been selected once. Here is how the back-end data looks like (this is in a separate tab while the main drop-down is in a tab named ‘Drop Down No Repetition’).
Here is how you can create this back-end data:
- Column B (Member List) has the list of all the members (or items) that you want to show in the drop-down list
- Column C (Helper Column 1) uses a combination of IF and COUNTIF functions. This gives the name if the name has not already been used, else it gives a blank.
=IF(COUNTIF('Drop Down No Repetition'!$C$3:$C$7,B3)>0,"",B3)
- Column D (Helper Column 2) uses a combination of IF and ROWS functions. This gives the serial number if the name has not been repeated, else it gives a blank.
- Column E (Helper Column 3) uses a combination of IFERROR, SMALL, and ROWS. This stacks all the available serial numbers together.
- Column F (Helper Column 4) uses a combination of IFERROR and INDEX functions. This gives the name that corresponds to that serial number.
- Use the following steps to create a dynamic named range
- Go to Formula –> Name Manager
- In the Name Manager dialogue box, select New
- In the New Name Dialogue Box, use the following details
- Name: DropDownList
- Refers to: =List!$F$3:INDEX(List!$F$3:$F$9,COUNTIF(List!$F$3:$F$9,”?*”))
This formula gives a range that has all the names in column F. It is dynamic and updates as the names change in Column F.
- Go to Tab Drop Down No Repetition, and create a data validation drop-down list in cell range C2:C6. Here are the steps to do this:
- Go to Data –> Data Tools –> Data Validation
- In the Data Validation dialogue box, use the following:
- Validation Criteria: List
- Source: =DropDownList
- Click OK
Now your drop down list is ready, where once an item is selected, it does not appear in subsequent drop-downs.
Other Useful Articles on Drop-Down Lists in Excel: