Forum Discussion

JTB76's avatar
JTB76
Copper Contributor
Mar 02, 2025

Drop Down Lists and Vlookups

Hi - Trying to understand if there is a way to do the following in the Process Name(s) column in my spreadsheet:

 

The Plan Name column has a drop down option with a link to a list of plans.  When you click on the relevant plan for you, the Plan ID and Primary Resilience Rep data is pulled through via Vlookup.  What I now need to do, is to be able to have a drop down on each row in the Process Name(s) column that will contain only those Processes that are associated with the selected Plan ID.  The user can then select multiple processes (one per row).  Its almost like I need to do a cross between a drop down list and a Vlookup.

 

Any help much appreciated

Thanks

James

 

 

excel
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Mar 10, 2025

    Considering this:

     

    Step 1: Organize Your Data
    Ensure your data is structured appropriately:

    • A table with Plan IDs and their corresponding Processes (e.g., a "Process Mapping" table).
    • Your existing table with columns for Plan Name, Plan ID, and Process Name(s).

    Step 2: Create Named Ranges for Each Plan ID
    Use named ranges to define the list of Processes associated with each Plan ID:

    • In the "Process Mapping" table, filter for each Plan ID and the corresponding Processes.
    • Assign a named range for each Plan ID (e.g., "Plan1Processes", "Plan2Processes").

    Step 3: Use Dependent Data Validation
    In the Process Name(s) column, set up a drop-down list using Data Validation:

    • Go to Data > Data Validation.
    • In the "Allow" field, select List.
    • In the "Source" field, use the INDIRECT function to reference the named range based on the selected Plan ID:
    =INDIRECT(A2)

    Step 4: Allow Multiple Selections
    Since Excel doesn't natively support multiple selections in a drop-down, you'll need to use VBA (Visual Basic for Applications) for this feature:

    • Press Alt + F11 to open the VBA editor.
    • Insert a new module and paste the following code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    Dim OldValue As String
    Dim NewValue As String
    
    On Error Resume Next
    If Not Intersect(Target, Range("B:B")) Is Nothing Then 'Adjust column range as needed
        Application.EnableEvents = False
        If Target.Cells.Count > 1 Then Exit Sub
        NewValue = Target.Value
        Application.Undo
        OldValue = Target.Value
        Target.Value = NewValue
        If OldValue <> "" Then
            If NewValue <> "" Then
                Target.Value = OldValue & ", " & NewValue
            Else
                Target.Value = OldValue
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub
    • Adjust the range in the code to fit your "Process Name(s)" column.
    • Close the VBA editor and save your workbook as a Macro-Enabled Workbook (.xlsm).

Resources