$40 USD: Dynamic Dependent Data Validation List

  • In the attached file I have built 2 dynamic data validation lists in the Sheet (Picklists) based off column data in a another sheet, The Data Validation List exclude blanks and cells with formulas that result in blanks from the reviewer and evaluator columns in the 'SurveyExport' sheet. Now I am looking to add a set of dependent pick lists where the first is a unique list of names from the column called "Survey Name". The the second (dependent) pick list would be every name in the "Evaluator" column where the "Survey Name" value is listed. For example, if "Sanchez, Fernando" was picked in the 'Survey Name' pick list, then in the Evaluator picklist would only have "Smith, Lisa" and "Brown., Nadia" available to select from. Prepayment has been sent.


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 171"]Survey Name[/TD]
    [TD="width: 171"]Evaluator[/TD]

    [/tr]


    [tr]


    [TD="width: 171"]Sanchez, Fernando[/TD]

    [td]

    Smith, Lisa

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Sanchez, Fernando[/TD]

    [td]

    Brown, Nadia

    [/td]


    [/tr]


    [tr]


    [td]

    Sanchez, Fernando

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    Smith, Jim

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    Black, Bill

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    Gill, Melissa

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    Mayfield, Baker

    [/td]


    [/tr]


    [/TABLE]

    [ATTACH]n1210336[/ATTACH]

  • Yes....that is it. AWESOME!! Thank you!! Now if I wanted to keep my 2 original pick lists and then add the dependent one your just created separately, how should I do this? Or could you post a revsion with that?

  • if using makro
    for auto call or make in worksheet_selectionChange

    Code
    1. Sub aa()
    2. Dim x
    3. Sheets("Picklists").[A2:A1000].ClearContents
    4. x = Filter(Evaluate("TRANSPOSE(IFERROR(IF(SurveyExport!EZ1:EZ10=" & Chr(34) & [g2].Value & Chr(34) & ",(SurveyExport!FA1:FA10)),FALSE))"), False, 0)
    5. If UBound(x) >= 0 Then
    6. Sheets("Picklists").[a2].Resize(UBound(x) + 1) = Application.Transpose(x)
    7. End If
    8. End Sub