asked on

Excel: How would I make a depleting dropdown list that shows only values that havent been chosen before?

Excel: Can I make a dropdown list that removes the dropdown's selected values from the dropdown list in future iterations & builds a list of previously used values?

List named "USERS" originally contains all available Users. In my file, the selected user dropdown is used to populate dependent cells which can be verified or corrected. When completed for that user, the data is copied to another worksheet & I'd pick the next user from the dropdown list.
I would like to have my list of users shrink as I complete the iterations & updates so that the users that I have completed wouldnt appear in my dropdown list, but would be added to the list of completed users.
User_Lists_Example.xlsx

Joe Howard

Put this code in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("F2"), Target) Is Nothing Then
        With ActiveSheet.Range("B:B")
            Set rng = .Find(What:=Range("F2").Value, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        End With
        rng.Cut Destination:=Range("D" & ActiveSheet.Columns("D").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row + 1)
    End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Mike in IT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zorvek (Kevin Jones)

Attached is a sample workbook illustrating how to create your depleting dropdown list without using VBA and using only formulas. The idea is to use a second worksheet that has three lists: a master list of names, a filtered list of names that have not been used, and a third list of those unused names in a condensed form. A named formula produces the final "range" that contains only the unused names without any blank entries.

Kevin
-Depleting-Dropdown-List.xlsx

Michael Spellman

ASKER

Thanks very much. This makes things much easier!