title: Dynamically generated multi select drop down list in Excel
body: <p>Need to know if it is possible to create a multi select drop down list in excel which in turn dynamically links to another multi select drop down list.</p>
  
  <p>For example, consider two columns namely Country and City as follows</p>
  
  <pre><code>India -&gt; Delhi, Mumbai, Bangalore;
  Australia -&gt; Sydney, Hobart, Brisbane, Perth;
  USA -&gt; CA, NYC, LA;
  Pakistan -&gt; Lahore, Karachi, Peshawar
  </code></pre>
  
  <p>Suppose if i select India and Australia in my first drop down, i should have an option to select one or more options in Delhi, Mumbai, Bangalore, Sydney, Hobart, Brisbane and Perth only.</p>
  
  <p>EDIT: I have been able to create a multi select drop down list and a single select drop down that dynamically updates according to the single value selected in the parent column. I am not able to combine both of them</p>
  
  <pre><code>    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Oldvalue As String
      Dim Newvalue As String
      Dim Extract As String
  'Dim arr As Variant
  Dim i As Integer
  Dim ComboList As String
  
  Application.EnableEvents = True
  On Error GoTo Exitsub
  If Target.Column = 8 Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
      GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
      Application.EnableEvents = False
      Newvalue = Target.Value
      Application.Undo
      Oldvalue = Target.Value
        If Oldvalue = "" Then
          Target.Value = Newvalue
        Else
          If InStr(1, Oldvalue, Newvalue) = 0 Then
              Target.Value = Oldvalue &amp; ", " &amp; Newvalue
        Else:
          Target.Value = Oldvalue
        End If
      End If
    End If
  End If
  Application.EnableEvents = True
  Exitsub:
  Application.EnableEvents = True
  
  If Target.Column = 9 Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
      GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
      Application.EnableEvents = False
      Newvalue = Target.Value
      Application.Undo
      Oldvalue = Target.Value
        If Oldvalue = "" Then
          Target.Value = Newvalue
        Else
          If InStr(1, Oldvalue, Newvalue) = 0 Then
              Target.Value = Oldvalue &amp; ", " &amp; Newvalue
        Else:
          Target.Value = Oldvalue
        End If
      End If
     End If
  
  
      TargetRow = ActiveCell.Row
      MsgBox (TargetRow)
      TargetCol = ActiveCell.Column
  
      Dim CLInd As String: CLInd = "Delhi, Mumbai, Bangalore"
      Dim CLAus As String: CLAus = "Sydney, Hobart, Perth, Brisbane"
      Dim CLPak As String: CLPak = "Lahore, Karachi, Peshawar"
      Dim CLNZ As String: CLNZ = "Auckland,Wellington"
      Dim CLUSA As String: CLUSA = "CA, LA, DC"
  
  
      Extract = Index(A2:J10, TargetRow - 1, 9)
  
  
  
  
      If InStr(1, Extract, "IND") &lt;&gt; 0 Then
         ComboList = ListJoin(ComboList, CLInd)
      End If
  
      If InStr(1, Extract, "AUS") &lt;&gt; 0 Then ComboList = ListJoin(ComboList, CLAus)
      End If
  
      If InStr(1, Extract, "PAK") &lt;&gt; 0 Then ComboList = ListJoin(ComboList, CLPak)
      End If
  
      If InStr(1, Extract, "NZ") &lt;&gt; 0 Then ComboList = ListJoin(ComboList, CLNZ)
      End If
  
      If InStr(1, Extract, "USA") &lt;&gt; 0 Then ComboList = ListJoin(ComboList, CLUSA)
      End If
  
    End If 'target column=9
  
    Call UpdateCombo(Target.Value, ComboList)  'target.value or target.range
  
  End Sub
  
  Private Function ListJoin(Str1 As String, Str2 As String) As String
      If Str2 = "" Then ListJoin = Str1
      If Str1 = "" And ListJoin = "" Then ListJoin = Str2
      If ListJoin = "" Then ListJoin = Str1 &amp; "," &amp; Str2
  End Function
  
  Private Function UpdateCombo(ByVal Target As Range, ComboList As String)
      With Target.Validation
          .Delete
          .Add Type:=xlValidateList, Formula1:=ComboList
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
      If InStr(ComboList, Target.Value) = 0 Then Target.Value = ""
  End Function
  </code></pre>