Watch Video – Creating Multiple Drop-down Lists in Excel without Repetition
Excel Drop Down Lists are intuitive to use and extremely useful in when you are creating an Excel Dashboard or a data entry form.
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.
=IF(C3<>"",ROWS($C$3:C3),"") - Column E (Helper Column 3) uses a combination of IFERROR, SMALL, and ROWS. This stacks all the available serial numbers together.
=IFERROR(SMALL($D$3:$D$9,ROWS($D$3:D3)),"") - Column F (Helper Column 4) uses a combination of IFERROR and INDEX functions. This gives the name that corresponds to that serial number.
=IFERROR(INDEX($B$3:$B$9,E3),"") - 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.
Try it yourself.. Download the file
Other Useful Articles on Drop-Down Lists in Excel:
14 thoughts on “Creating Multiple Drop-down Lists in Excel without Repetition”
also, right now it is delimited by a comma. and i want to apply the new line to all the cells it 4 columns and and i have 504 lines for each unit person
Question, So lets say i orginally set up for mulitple colums to select form a drop down. 1. how can i apply the next line to all the cells without haveing to reselect? and 2 how can it auto alphabitized the list ( ie i hae trust names for my drop donw_ so if i have ABC Trust, KMJ Trust and YXF Trust but I selected them in different order so it KMJ, ABC adn YXF but i want it to be ABC, KMJ adn YXF.
Hi,
I am using multiple drop down lists but they are from different sources. It is for a rota where some people have more skills and therefore can be used more than others.
The rota lists all the roles needed so each role has a drop down which comes from its own independent source list.
What I am trying to do is when I have selected one person they either:
do not appear in the other drop down lists or
An error message comes up when a name has been selected more than once.
By using different source lists, this is complicating the issue but as not all people can do all roles, i cannot see another option.
Did you ever figure this out? I am trying to do the exact same thing.
Thanks! Having trouble though. When I add a name everything works fine all the way to the numbers re-stacking (in helper column 3) but then all the names on the list get repeated in helper column 4.
i.e.
I Start with Bill, Jo, Frank and Susan. The first person assigned is Susan, In Column 4 the following shows up.
Bill
Jo
Frank
Bill
Jo
Frank
Susan
Any ideas? Im working in Google docs. Maybe that’s the problem?
I think it’s the Google Sheets issue.
Invaluable writing ! For my two cents , if someone require to merge PDF or PNG files , We discovered a service here http://goo.gl/1widdK
I am working on a simple form using excel. I have 4 cells with drop
down lists where I have the following code (with your help) to allow for multiple
selections without repetition.
Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from http://www.trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = “$C$10” Or Target.Address = “$C$16” Or Target.Address = “$C$17” Or Target.Address = “$C$19” Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
My problem is that I need to protect the sheet. When I do this, the
user can still access the drop down list but the option for multiple
selections without repetition quits working. How can I fix this issue.
Thanks in advance for any assistance.
I have the same issue Emily, did you find the answer?
Hey Julie,
To make sure the code works on protected sheets, add the following line of code to the macro (right after the Dim statement).
Me.Protect UserInterfaceOnly:=True
Also, make sure the cell that has the drop-down list is not protected (when you protect the entire sheet. You can do that by changing the Locked property). You can read more about it here: https://trumpexcel.com/lock-cells-in-excel/
How can you expand this to use for say 100 items in your drop down list.
Hello Emily.. You can expand this to 100 items by changing the references in the formulas and the named range. Here is a template where I have added 100 options in the drop down: https://www.dropbox.com/s/5b9ksmkim4ynv6g/Multiple%20Drop%20Down%20Lists%20without%20Repetition%20-%20100%20Options.xlsx?dl=0
Love this! It’s possible to do with only three helper columns, though. You can remove the first helper column if you use this in the second:
=IF(ISERROR(MATCH(B3,’Drop Down No Repetition’!C$3:C$7,0)),MATCH(B3,B$3:B$9,0),””)
Always a fan of reducing helper columns!
Thanks for sharing Shanks 🙂 Glad you liked it. And you are right, the first helper column could have been avoided. Just wanted to make it simple to follow in the article.