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). 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: 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:
How to Create a Dependent Drop-Down List in Excel. Extract Data from Drop-Down List Selection in Excel. Disguise Numbers as Text in a Drop-Down List. Create a Drop-Down List with Search Suggestions. Multiple Selection from a Drop-Down List in a Single Cell. How to Make a Yes/No Drop-Down in Excel?
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. 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? 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. 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/ =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!
