Drop Down Lists and Vlookups

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).