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.
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.
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.
___________________
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.
Thanks, Vitalie, the correct file is at that link now.
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
I recalled that you introduced another approach for setting sitting plan. Nice to learn another one! 🙂
Thanks.
seating plan…
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
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?
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!