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

 

[James006]

Hi and Welcome to the Amazing World of Excel

In my humble opinion, the best available clear explanation is Debra's

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

 

[JasonRafton]

Hi and Welcome to the Amazing World of Excel

In my humble opinion, the best available clear explanation is Debra's

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

Thanks for that lead. I have tried to do that same procedeure before posting this thread. Am I right in thinking that to do this, I need another sheet for the DATA. I can't really use a data sheet as each day a new sheet is made for that day and at the end of the month a new book is started. I'm afraid the data sheet would not be transferred each month and TBH it is quite confusing to get it to work with the sheet I already have that has a list in the K column. I think I'm out of my depth already.

 

[James006]

Hi,

Below is an event macro to be placed in the Sheet Module
You do need to Adjust Both Dependent Validation Lists with your own choices ...

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, 7).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Sub1,Sub2,Sub3"
        End With
    ElseIf Target.Value = "WIP" Then
        With Target.Offset(0, 7).Validation
            ' Adjust with your Own choices in Formula1:
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Wip1,Wip2,Wip3"
        End With
    End If
End Sub

Hope this will help

 

[JasonRafton]

Hi,

Below is an event macro to be placed in the Sheet Module
You do need to Adjust Both Dependent Validation Lists with your own choices ...

[/QUOTE]

[QUOTE="James006, post: 6012876, member: 115324"]
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, 7).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Sub1,Sub2,Sub3"
        End With
    ElseIf Target.Value = "WIP" Then
        With Target.Offset(0, 7).Validation
            ' Adjust with your Own choices in Formula1:
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Wip1,Wip2,Wip3"
        End With
    End If
End Sub

Hope this will help

Is there anything other than the SUB1,SUB2...and the WIP1,WIP2.... choices that I need to change? Do i need to put column positions in somewhere or something. Cause it doesn't work as is. Or does Formula 1 and Formula 1 need to be Formula 1 and Formula 2?

 

[JasonRafton]

Got it!! I think.....I'll be back

 

[James006]

Hi,

You are right, there is nothing else to change but both your lists' elements ...

 

[JasonRafton]

Yeah got it going. I had to change 0, 7 to 0, 8 for the 8th cell. Little thingi worked out but I felt great. Hahaha
Thankyou Thankyou Thankyou

 

[James006]

Well done

 

[JasonRafton]

Okay, last gliche.
When I try to type manually a name into Column K, an error window comes up. How can I get around this?