Creating a Dependent Drop Down Menu without a Separate Defined List

[cadams83]

Hi All,

I am trying to create a dependent data validation drop down menu without having a separate defined List for each variable. Here is my example data (please note this table is filled dynamically when a user enters information, which is why I can't have a prior defined list):

Reference Sheet:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client Name[/TD]
[TD]Project ID[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Project A-1[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Project A-2[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Project A-3[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Project A-4[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Project B-1[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Project B-2[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Project B-3[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Project B-4[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Project B-5[/TD]
[/TR]
</tbody>[/TABLE]


I am trying to make a drop down based on a user's entry of Client A or Client B in the Client Name column. So if they enter Client A, they will have a drop down menu of Project A-1, Project A-2, etc. However, since the above reference chart is created dynamically when a user enters data (i.e. they could add a new Project ID for Client A - or even a Client C), I would like to use a formula in the Data Validation list which could pass the correct Project IDs without having a predefined "ClientAList".

Any thoughts or suggestions would be much appreciated!

Thanks!
Chris

 

[L. Howard]

Hi Chris,

This should do what that, if I am reading your post correctly.

Copy code in its entirety to the sheet module.

Column A is Client Name header and list, column B is Project ID header and list.

In cell D1, install a data validation drop down > check > List and in source window =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

Select a item from the drop down in D1 and the code will produce a drop down in E1 with the list pertinent to the B1 selection.

Add to the lists as you desire, the OFFSET formula will capture the new items (up to 200)

You will most likely want different ranges for your working sheet, this will demo the concept and you can adjust the ranges to suit for you real sheet.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'/// by: Siddharth Rout

    Dim i As Long, LastRow As Long, n As Long
    Dim MyCol As Collection
    Dim SearchString As String, TempList As String
 
    Application.EnableEvents = False
 
    On Error GoTo Whoa
 
    '~~> Find LastRow in Col A
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Set MyCol = New Collection
 
        '~~> Get the data from Col A into a collection
        For i = 1 To LastRow
            If Len(Trim(Range("A" & i).Value)) <> 0 Then
                On Error Resume Next
                MyCol.Add CStr(Range("A" & i).Value), CStr(Range("A" & i).Value)
                On Error GoTo 0
            End If
        Next i
 
        '~~> Create a list for the DV List
        For n = 1 To MyCol.Count
            TempList = TempList & "," & MyCol(n)
        Next
 
        TempList = Mid(TempList, 2)
 
        Range("D1").ClearContents: Range("D1").Validation.Delete
 
        '~~> Create the DV List
        If Len(Trim(TempList)) <> 0 Then
            With Range("D1").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                 xlBetween, Formula1:=TempList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    '~~> Capturing change in cell D1
    ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
        SearchString = Range("D1").Value
 
        TempList = FindRange(Range("A1:A" & LastRow), SearchString)
 
        Range("E1").ClearContents: Range("E1").Validation.Delete
        [E1].Activate
        If Len(Trim(TempList)) <> 0 Then
            '~~> Create the DV List
            With Range("E1").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                 xlBetween, Formula1:=TempList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    End If
 
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub
 
'~~> Function required to find the list from Col B
Function FindRange(FirstRange As Range, StrSearch As String) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Dim strTemp As String
 
    Set aCell = FirstRange.Find(what:=StrSearch, LookIn:=xlValues, _
    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
 
    ExitLoop = False
 
    If Not aCell Is Nothing Then
        Set bCell = aCell
        strTemp = strTemp & "," & aCell.Offset(, 1).Value
        Do While ExitLoop = False
            Set aCell = FirstRange.FindNext(After:=aCell)
 
            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                strTemp = strTemp & "," & aCell.Offset(, 1).Value
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = Mid(strTemp, 2)
    End If
End Function

 

[cadams83]

Hi Howard,

Thanks for your quick response!! This is great! I had a couple of follow up questions and they both address the fact that (in our example here) Columns D/E are where users will be both (A) adding new Client Names/Project IDs (which automatically get added to the lookup list) and (B) using the generated drop down. This leads to two questions on the Macro (which is amazing!):

1) Is it possible to retain the Data Validation list in Column D rather than rewriting and inputting a set list? It is current referencing a defined list of unique Client Names, which updates if there are other Clients added down the line. Also, it needs to allow entries outside of what is currently in the list as this is how the list is generated. I tried to modify this in the macro and managed to break it...
2) (and probably more important) Since this is utilized for the whole Client Name/Project ID columns, is it possible to loop through all rows (or at least the first 300)? So if there is an entry, for example, in D25, it would generate a new DV in E3?

Again, this is excellent! I was beginning to despair that it was even possible.

Thanks Howard!
Chris

 

[L. Howard]

Hi Chris,

Hmmm, not following the modifications you want to make.

Can you post a link to your workbook here, and on the sheet explain where what etc. you want to happen, referring to specific cells, columns rows and such?

You can post a link to a workbook here, but not download one. I use Drop Box, but there are others.

Howard