Solved

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

Posted on 2016-11-28
4
101 Views
Last Modified: 2016-12-01
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
0
Comment
Question by:Michael Spellman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41905769
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

0
 
LVL 7

Accepted Solution

by:
Mike in IT earned 500 total points
ID: 41905775
To do this I have used some VBA code. To start everything off you will need to add the following to a module in VBA:
Validate: you will have to run this before the others to create and fill the drop down. You might want to run this before copying the others into your module

Sub Validate()
Dim MyList() As String
Dim Users() As String
Dim cellNumber
Dim MyIndex

cellNumber = 6
'removes empty cells in the list of users
Range("B6:B" & Cells(Rows.Count, 2).End(xlUp).Row).Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
'counts how many users are left
n = Worksheets("Lists").Range("B6:B19").Cells.SpecialCells(xlCellTypeConstants).Count
ReDim MyList(n)
ReDim Users(n)
'Add user to the list of users MyList
For i = 0 To n Step 1
    MyList(i) = Replace(Range("B" & cellNumber).Value, ",", ";") 'had to change the "," to a ";" so that the list would work correctly
    Range("B" & cellNumber).Value = Replace(Range("B" & cellNumber).Value, ",", ";")
    cellNumber = cellNumber + 1
Next i
'add the MyList of users to the drop down
With Range("F6").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(MyList, ",")
End With

End Sub

Open in new window


Private Sub Worksheet_Change(ByVal Target As Range)
'This looks for a change in the worksheet and then runs the following code.
'You may need to update based on what you are working on.
Dim MyIndex
Dim Counted
Dim LastRow
If Target.Cells.Count > 1 Then Exit Sub
    On Error GoTo Whoa

    Application.EnableEvents = False 'turn events off so that you don't create an endless loop
    'count the number of users
    n = Worksheets("Lists").Range("B6:B19").Cells.SpecialCells(xlCellTypeConstants).Count
    MyIndex = ValListIndex("F6") 'find out where in the list is the selected user
    'move the selected user to the "Complete" list
    If MyIndex > 0 Then
        Range("B" & (5 + MyIndex)).Select
        Selection.Cut
        LastRow = Cells(Rows.Count, 4).End(xlUp).Row
        Range("D" & (LastRow + 1)).Select
        ActiveSheet.Paste
        Call Validate
    End If
    
    'Turn the event listener back on
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Open in new window


Public Function ValListIndex(ByVal Target As String) As Long
    ' Return the ListIndex of the value in Target
    ' Created by Sisyphus @ ExcelKey von Feb 21, 2012

    Dim Cell As Range
    Dim ValList As String
    Dim ValItem() As String
    Dim LiSep As String
    Dim i As Integer
    
    On Error GoTo ErrExit
    Set Cell = ActiveSheet.Range(Application.ConvertFormula _
                          (Target, xlA1, xlA1, xlAbsolute)). _
                           Cells(1)
    ValList = Cell.Validation.Formula1
    If Len(ValList) = 0 Then GoTo ErrExit
    LiSep = Application.International(xlListSeparator)
    ValItem = Split(ValList, LiSep)
    For i = UBound(ValItem) To 0 Step -1
        If i >= LBound(ValItem) Then
            If ValItem(i) = Cell.Value Then Exit For
        End If
    Next i
    ValListIndex = i + 1
    Exit Function
    
ErrExit:
    ValListIndex = -1
End Function

Open in new window


You will have to run the sub "Validate" first before you can do anything else as it creates the drop down and fills it. You may need to add a line like `Application.EnableEvents = False` in the beginning and then one that is `Application.EnableEvents = True` at the end so that you don't trigger the event listener, then comment them out once you've run it once. Then the drop down will be filled and selecting a name will move it to the "Completed_Users" column and out of the drop down.

This should be enough to get you started.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 41906207
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
1
 

Author Closing Comment

by:Michael Spellman
ID: 41909642
Thanks very much.  This makes things much easier!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question