Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
is this a job for vlookup? office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx –  Nick Josevski Feb 14 '12 at 2:57
1  
are you looking for cascading validation? –  JMax Feb 14 '12 at 10:02
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.