I’ve just had to work out how to change the cell drop down source of an Excel cell based on the value of another cell. In this case I had one drop down with the Australian states in it and I wanted this to drive a second dropdown that allows users to select the Federal Electorate. It turned out to be pretty easy (and quite nifty). My spreadsheet is setup with a series of named ranges containing the state electorates. So, for example, there’s a named range called ElectoratesNSW containing all the electorates for NSW. Just add the following to the sheet where you want the data validation magic to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sState As String
  Dim rngTmp As Range
  '
  ' Cell in row 7 and column 3 is the State dropdown
  '
  If Target.Row = 7 And Target.Column = 3 Then
    '
    'Cell in row 7, column 4 is the electorate dropdown
    '
    Set rngTmp = Me.Cells(7, 4)
    If Len(Target.Value) > 1 Then
      sState = Target.Value
      rngTmp.Formula = ""
      With rngTmp.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & "Electorates" & UCase(sState)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      End With
    Else
      rngTmp.Validation.Delete
      rngTmp.Formula = ""
    End If
  End If

End Sub