Multi-level dependent lists

[aussiemate]

Hi,

I need to set up an excel sheet with dependent data validation that is multiple levels deep.

ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.

Is this possible?

Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.

Really appreciate any help you can provide.

 

[aussiemate]

Thanks for pointing out the typo.

I've had to dummy out several lists (the a, b, c etc) to protect content. There may be some redundency but it works so I'll leave it be.

I was wondering if you could let me know how to set up the marco like in your sample file to auto blank out dependant cells if a higher value is changed. So if I change a G1 value it clears cells for G2, G3 etc. So I don't end up with Plant>Canine>Gorrilla.

 

[jbeaucaire]

Using the same syntax shown above in post #6, and correcting for your actual columns of N:Q:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("N2:N1000")) Is Nothing Then
        Range(Cells(Target.Row, "O"), Cells(Target.Row, "Q")).Value = ""
    ElseIf Not Intersect(Target, Range("O2:O1000")) Is Nothing Then
        Range(Cells(Target.Row, "P"), Cells(Target.Row, "Q")).Value = ""
    ElseIf Not Intersect(Target, Range("P2:P1000")) Is Nothing Then
        Cells(Target.Row, "Q").Value = ""
    End If
Application.EnableEvents = True
End Sub

1) Right-click the Data sheet tab
2) Select View Code
3) Paste in the code above
4) Close the VB editor
5) Save your workbook as a macro-enabled workbook

 

[aussiemate]

Okay cool. And do I just paste it again for S-T and U-V? If there are 3 versions of the macro running on the same sheet do I have to change anything? Do I put them all one after another or do they have to be in separate macro windows/pages?

 

[jbeaucaire]

No, there can only be ONE ws_change macro in the sheet module, so you'll need to read that macro so far and understand what each 'section' is doing. Once you can do that, you can continue to add as many other 'sections' as you need.

I used the same methodolody to test 3 different columns, each test caused a different resulting range to be cleared, but it's the same process in each 'section'.

1) test if the change occurred in a specific column, and if so
2) clear the cells on the same row of interest.

Note the first test in the current code tests column N? If the change is in column N, it then clears O:Q on the same row.

The next test is for column O, if in column O, it clears the P:Q values.

The last test is for column P, if in column O, it just clears Q on that row.

Since you're needed additions are only two columns each, then you can just use the last test as a template to add two more ElseIf sections. You'll be adding in another two-line ElseIf section for your "S" column test and your "U" column test.

Give it a try and post your version, we'll let you know if you got it.

 

[aussiemate]

So this should do the trick?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("N2:N1000")) Is Nothing Then
        Range(Cells(Target.Row, "O"), Cells(Target.Row, "Q")).Value = ""
    ElseIf Not Intersect(Target, Range("O2:O1000")) Is Nothing Then
        Range(Cells(Target.Row, "P"), Cells(Target.Row, "Q")).Value = ""
    ElseIf Not Intersect(Target, Range("P2:P1000")) Is Nothing Then
        Cells(Target.Row, "Q").Value = ""
    ElseIf Not Intersect(Target, Range("S2:S1000")) Is Nothing Then
        Cells(Target.Row, "T").Value = ""    
    ElseIf Not Intersect(Target, Range("U2:U1000")) Is Nothing Then
        Cells(Target.Row, "V").Value = ""
End If
Application.EnableEvents = True
End Sub

 

[jbeaucaire]

Hehe...did you test it out?


Looks good from here.

 

[jbeaucaire]

Since the last 3 lines of code all do the same thing...check one column for a change and clear the cell to the right, you can actually merge those lines into one like so...just for chuckles:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("N2:N1000")) Is Nothing Then
        Range(Cells(Target.Row, "O"), Cells(Target.Row, "Q")).Value = ""
    ElseIf Not Intersect(Target, Range("O2:O1000")) Is Nothing Then
        Range(Cells(Target.Row, "P"), Cells(Target.Row, "Q")).Value = ""
    ElseIf Not Intersect(Target, Range("P2:P1000,S2:S1000,U2:U1000")) Is Nothing Then
        Target.Offset(0, 1).Value = ""
    End If
Application.EnableEvents = True
End Sub

This isn't as easy to read, but it's interesting. FYI.

 

[aussiemate]

Thanks. VB is way over my head. I've never had to program anything! I can get round excel fine but no plan to learn VB.

Thanks for your help!

 

[ChainCrane]

Hi Jerry,
Any sample file on 4 level dependent drop down lists?

Thanks,
CC

Here's a sample sheet I've posted showing how it's done. The sheet "CHOICES" has the dependent drop lists (and shows one of the dangers of using them) and the sheet "LISTS" shows the lists and named ranges in use.

Have a look.

Sample File
Jerry Beaucaire's Excel Tools
The files you want is:
--DependentLists3.xls - 3 levels

More reading:
http://www.contextures.com/xlDataval02.html
http://www.contextures.com/xlDataVal13.html

 

[jbeaucaire]

My most current Dynamic Cascading Lists is found here:

Dynamic Cascading Drop Down Lists

It's version 4.