Microsoft Excel validation based on adjacent cell

Question

I have a two adjacent columns named Country and Rating. The Country column has a dropdown to select which country a DVD is being delivered to. It is using a list validation to create the dropdown:

=$AN$31:$AN$53   # [US, CA, JP, etc.]

What I need to do is display a validation list for Rating based upon the value of the Country column. For example,

if Country=US, Rating dropdown = [G, PG, PG-13, R]
if Country=CA, Rating dropdown = [G, PG, 14A, 18A, R]

How would I do this in excel?

Answer 1

Okay. So I did this via VBA. Say your initial validation is in D2. Place this in the code of sheet1 (right click - view code)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(True, True) = "$D$2" Then
        Select Case Target
            Case "A"
                Call Macro1
            Case "B"
                Call Macro2
            Case "C"
                Call Macro3
            Case "D"
                Call Macro4
            Case "E"
                Call Macro5
            Case Else
                'Do nothing
        End Select
    End If

End Sub

Once you have that in the code of the sheet, create a module and use this:

Sub Macro1()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$A$9:$A$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
       End With
End Sub


Sub Macro2()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$B$9:$B$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub


Sub Macro3()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$C$9:$C$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub



Sub Macro4()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$D$9:$D$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub


Sub Macro5()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$E$9:$E$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

All you need now is to change the list coordinates and everything will work perfectly.