CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2010dropdown
Mike
Mike

Reputation: 1938

How to create a dynamic drop list in Excel

I've got a spreadsheet that I'm trying to create a dynamic drop list in. I have it setup so that I have a list of Test No. and the Day of the week next to it. It looks like this:

Setup TestNumbers

Now what I want is as I add the same number under one of the lists I want the drop down list to only give me the available days that are not already used for that number.

i.e. for 1234 the drop down under New should have Thu, Fri, Sat, Sun i.e. for 5678 the drop down under New should have Mon, Wed, Thu, Sat, Sun i.e. for 9012 the drop down under New should have tue, Sat, Sun

I have a named range that has the 7 days of the week and I can use Data Validation to have that list be the drop options, but I want it to be dynamic and only give me the options that are not already used by that Test No.

Can this be done?

Upvotes: 0

Views: 888

Answers (3)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

Assuming your data is in Columns A:B where row1 is header row and you have a named range called Days then right click the Sheet tab --> View code and paste the code given below into the opened code window --> Save your workbook as Macro-Enabled Workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim x, dict
Dim i As Long, lr As Long
Dim Rng As Range, Cell As Range
Dim Str As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("A2:A" & lr)
x = Range("Days").Value
Set dict = CreateObject("Scripting.Dictionary")
If Target.Column = 2 And Target.Row > 1 Then
    If Target.Offset(0, -1) <> "" Then
        For Each Cell In Rng
            If Cell <> "" And Cell = Target.Offset(0, -1) Then
                If Str = "" Then
                    Str = Cell.Offset(0, 1).Value
                Else
                    Str = Str & ", " & Cell.Offset(0, 1).Value
                End If
            End If
        Next Cell
        For i = 1 To UBound(x, 1)
            If InStr(Str, x(i, 1)) = 0 Then
                dict.Item(x(i, 1)) = ""
            End If
        Next i
        On Error Resume Next
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                xlBetween, Formula1:=Join(dict.keys, ",")
        End With
    End If
End If
End Sub

So once you select a cell in column B, the code will add a drop down list excluding the days already selected for the specific test no. corresponding cell in column A. enter image description here

Upvotes: 1

A.S.H
A.S.H

Reputation: 29332

You can handle the Worksheet_SelectionChange event in the sheet's code module to change the validation list. Some checks are necessary to see if the new selected cell is one of those you want to validate; i.e. column B, an identifier in column A, etc. The checks in the routine below conform to your example data.

' Code Module of your worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.count > 1 Then Exit Sub
    If Target.Column <> 2 Or Target.row < 2 Then Exit Sub
    If Len(Trim(Target.Value)) > 0 Then Exit Sub
    If Len(Trim(Target.offset(, -1).Value)) = 0 Then Exit Sub

    Dim newList As String: newList = ",Sun,Mon,Tue,Wed,Thu,Fri,Sat"
    Dim r As Range: Set r = Target.offset(-1)
    Do Until Len(Trim(r.Value2)) = 0 Or r.offset(, -1).Value2 <> Target.offset(, -1).Value2
        newList = Replace(newList, "," & r.Value2, "")
        Set r = r.offset(-1)
    Loop
    With Target.Validation
        .Delete
        .Add xlValidateList, , , Mid(newList, 2)
    End With
End Sub

Upvotes: 1

yass
yass

Reputation: 869

You can use depending Drop Down List
Create your List Mon--Sunday Define Name wkday for example
Select Tue--Sunday and Define Name Mon
Select Wed--Sunday and Define Name Tue
Select Thu--Sunday and Define Name Wed
Select Fri--Sunday and Define Name Thu
Select Sat--Sunday and Define Name Fri
Select Sunday and Define Name Sat

You can select all the cells in which you need the Drop Down List:
Starting with Cell B2 for example below create Data Validation, List, in Source write:

=IF(OR(B1="",B1="Day"),wkday,INDIRECT(B1))

Upvotes: 0

Related Questions