Remove Used Items in Excel Drop Down

Remove Used Items in Excel Drop Down

There is a new sample file on my Contextures web site, which lets you pick players for each inning in a baseball game.

You could tweak the file a bit, and use it to assign employees to workstations each day of the week, or anything similar.

After assigning employees, remove used items in Excel drop down list, to prevent them from being assigned twice.

Names Disappear From List

Once you have assigned a player in an inning, that name disappears from the drop down list in that column. In the screen shot below, Mike has been assigned as the pitcher for inning one. When I open the list in the cell below, Mike’s name is missing – it used to be between Lee and Pat.

Used Names Disappear From Drop-Down List
Used Names Disappear From Drop-Down List

See the Assignments by Player Name

There was an older version of this sample file, and I have updated it to use Excel Tables. I’ve also added a sheet that shows the assignments by player name, and you can see how often each player was assigned to a position.

see the assignments by player name
see the assignments by player name

Download the File

To see the file, and how it works, you can download it from my Contextures website. On the Hide Used Items in Drop Down List page, go to the download section, and get the Baseball Players sample file.

The zipped Excel file is in xlsx format, and does not contain macros.

___________________

8 thoughts on “Remove Used Items in Excel Drop Down”

  1. Hi. In the DV0061 file, there are gaps in the list for names that have been assigned, but in the screenshots above you don’t have any gaps. How can this be done? You also mention that the file is xlsx, but it’s actually xls.

  2. It’s great work Debra. I’m always following any new posts in contextures.
    Let me plz introduce my way to do the same task “Remove Used Items in Excel Drop Down”.
    I used a pivot table. Steps as below:
    1. create new sheet with “calc” name
    2. enter the list items in a range in the “Calc” sheet
    3. in “Calc” sheet converter that rang into a table with 2 columns (“item”, “Selected”) columns headers. To automate adding new items later.
    4. enter the following formula in the “Selected” column “=if(countif(“the name of the range where the list items are going to be selected”),list item)>1)” this formula is going to give us 1 or 0 for selected, not selected respectively.
    5. create a pivot table based on that two columns table.
    6. filter the pivot table to show only unselected items
    7. create a dynamic defined range name to show always unselected items “UnselIems=offset(…..)”
    8. use the UnselItems define range in the cells validation
    9. vba code to referesh the pivot table when ever the selection of the entry sheet change using “intersect” function to specify the range.
    done
    hope i explain it will 🙂
    can i attach my work file
    hope to hear form u about my way
    thank you

  3. I recalled that you introduced another approach for setting sitting plan. Nice to learn another one! 🙂
    Thanks.

  4. Hi Debra, it is awesome. I have one question. Here is a formula to define PlayerPickList: =NamesUsed!M$5:INDEX(NamesUsed!M$5:M$15,MAX(NamesUsed!C$5:C$15),1) of course it works fine, but I am little confused, because the second part of formula (“INDEX(NamesUsed!M$5:M$15,MAX(NamesUsed!C$5:C$15),1)”) returns “Will”. Is it possible to define names using values instead of adresses? Thank you for your answer

  5. I have created conditional drop down screens. For instance, E27 will display a specific list based on the selection in cell D27 and so on. I, however, inserted the following code into the worksheet.
    Code 1: (allows the selection of multiple items in one cell separated by a comma)
    Developed by Contextures Inc.
    http://www.contextures.com
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim lUsed As Long
    If Target.Count > 1 Then GoTo exitHandler
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then GoTo exitHandler
    If Intersect(Target, rngDV) Is Nothing Then
    ‘do nothing
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 7 Or 8 Then
    If oldVal = “” Then
    ‘do nothing
    Else
    If newVal = “” Then
    ‘do nothing
    Else
    lUsed = InStr(1, oldVal, newVal)
    If lUsed > 0 Then
    If Right(oldVal, Len(newVal)) = newVal Then
    Target.Value = Left(oldVal, Len(oldVal) – Len(newVal) – 2)
    Else
    Target.Value = Replace(oldVal, newVal & “, “, “”)
    End If
    Else
    Target.Value = oldVal _
    & “, ” & newVal
    End If
    End If
    End If
    End If
    End If
    exitHandler:
    Application.EnableEvents = True
    End Sub
    The code allows for multiple selections with all drop down screens. Because of this, now Cell E27 won’t display a drop down screen, due to the fact that D27 has two or more selections. How do I make it so that cell E27 displays all the items relevant to the multiple selections in cell D27?

  6. Hi, this is exactly what I was looking for to help me sort a little league lineup.
    We have 12 player roster and I wanted to add 3 positions called “Bench1”, “Bench2”, “Bench3”. This would help track how many times someone was on the bench during a game. I’m not having the greatest success in modifying to accomplish what I’m trying to do. Any help please?
    Thanks!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.