Different drop down list depending on prev cell value

[JasonRafton]

Hi All,
An absolute newbie here. Trying to learn a new skill. I would like to have 2 different drop down lists in one column depending on what is selected in a previous cell.
In column D, if SUB is selected I would like a drop down list in Column K. If in column D, WIP is selected then I would like a different list in column K.
I know you guys will probably do this with your eyes closed but not me. Please be simple with your answers..... lol

 

[JasonRafton]

Below is the code I have on my sheet.
I need to be able to manually enter a NAME in column 8 if necessary without an error code coming up.
How can I do this?



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Target.Value = "SUB" Then
' Adjust with your Own choices in Formula1:
With Target.Offset(0, 8).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="KRUEGER,ROYANS,T/SALES,VAWDREY,VOLVO,BDS,HAMMAR"
End With
ElseIf Target.Value = "WIP" Then
With Target.Offset(0, 8).Validation
' Adjust with your Own choices in Formula1:
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="ALEX B,JYE P,LUKE K,BDS-ETHAN,BDS LACHLAN,OP-MATT,STT-TRAVIS,STT-ALEX,STT-ALF,STT-NICK S,STT-TIM,TSS-GERRY,TSS-TRENTE"
End With
ElseIf Target.Value = "OS" Then
With Target.Offset(0, 8).Validation
' Adjust with your Own choices in Formula1:
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="ALEX B,JYE P,LUKE K,BDS-ETHAN,BDS LACHLAN,OP-MATT,STT-TRAVIS,STT-ALEX,STT-ALF,STT-NICK S,STT-TIM,TSS-GERRY,TSS-TRENTE"
End With
End If
End Sub

 

[James006]

Hi,
Sorry ... but just tested again your macro and it is working as expected ... you can input a name in Column L

 

[James006]

Hi again,

Looks like you mean you want to allow the user to Input a Name NOT Present in the Data Validation List

Private Sub Worksheet_Change(ByVal Target As Range)
' Adjusted to Allow User to Input a Name NOT Present in the Data Validation List '''''''''
If Target.Column <> 4 Then Exit Sub
    If Target.Value = "SUB" Then
    ' Adjust with your Own choices in Formula1
        With Target.Offset(0, 8).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
            xlBetween, Formula1:="KRUEGER,ROYANS,T/SALES,VAWDREY,VOLVO,BDS,HAMMAR"
        End With
    ElseIf Target.Value = "WIP" Then
        With Target.Offset(0, 8).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
            xlBetween, Formula1:="ALEX B,JYE P,LUKE K,BDS-ETHAN,BDS LACHLAN,OP-MATT,STT-TRAVIS,STT-ALEX,STT-ALF,STT-NICK S,STT-TIM,TSS-GERRY,TSS-TRENTE"
        End With
    ElseIf Target.Value = "OS" Then
        With Target.Offset(0, 8).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
            xlBetween, Formula1:="ALEX B,JYE P,LUKE K,BDS-ETHAN,BDS LACHLAN,OP-MATT,STT-TRAVIS,STT-ALEX,STT-ALF,STT-NICK S,STT-TIM,TSS-GERRY,TSS-TRENTE"
        End With
    End If
End Sub

 

[JasonRafton]

Hi again,

Looks like you mean you want to allow the user to Input a Name NOT Present in the Data Validation List

Private Sub Worksheet_Change(ByVal Target As Range)
' Adjusted to Allow User to Input a Name NOT Present in the Data Validation List '''''''''
If Target.Column <> 4 Then Exit Sub
    If Target.Value = "SUB" Then
    ' Adjust with your Own choices in Formula1
        With Target.Offset(0, 8).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
            xlBetween, Formula1:="KRUEGER,ROYANS,T/SALES,VAWDREY,VOLVO,BDS,HAMMAR"
        End With
    ElseIf Target.Value = "WIP" Then
        With Target.Offset(0, 8).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
            xlBetween, Formula1:="ALEX B,JYE P,LUKE K,BDS-ETHAN,BDS LACHLAN,OP-MATT,STT-TRAVIS,STT-ALEX,STT-ALF,STT-NICK S,STT-TIM,TSS-GERRY,TSS-TRENTE"
        End With
    ElseIf Target.Value = "OS" Then
        With Target.Offset(0, 8).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
            xlBetween, Formula1:="ALEX B,JYE P,LUKE K,BDS-ETHAN,BDS LACHLAN,OP-MATT,STT-TRAVIS,STT-ALEX,STT-ALF,STT-NICK S,STT-TIM,TSS-GERRY,TSS-TRENTE"
        End With
    End If
End Sub

That is correct