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.

 

[jbeaucaire]

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

 

[aussiemate]

So glad the reply wasn't "Not possible"! I'll check out that link now. Thanks.

 

[jbeaucaire]

To answer your 2nd question, you can give the same "list of values in the same cells" multiple names, I do that frequently for things like this.

 

[aussiemate]

My fatal flaw, I was putting =indirect(a2) into the cell value not clicking data validation again! Silly me! Thanks a bunch!

The macro sounds great too! If I'm setting up a database and it has many rows of the same data validations as above, and I use that macro, will it only clear the later dependent cells in the specific row?

Edit: Just a note of a crosspost to:http://www.excelforum.com/excel-general/719517-multi-level-dependent-lists.html#post2260551 pretty much solved though! In like 10 mins! Whoo!

Edit: I'm lost with the marco. No clue how to get it on my document. Do you happen to have an awesome macro help file too?

 

[jbeaucaire]

This is the sheet-event macro in use on the sample workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A8")) Is Nothing Then
        Range("B8:C8").Value = ""
    End If
    If Not Intersect(Target, Range("B8")) Is Nothing Then
        Range("C8").Value = ""
    End If
End Sub

If you full describe the columns you've set up and the range of rows you want to monitor, the order in which the columns "flow", I can help tweak that macro and show you how to install it on your sheet.

 

[aussiemate]

Awesome. I made a dummy file but couldn't figure out how to attach it. For now I've put it as text below in 2 formats. Sorry its messy. The () show the links. (I) is independent list or text space, (A1) is stream A level 1, (A2) stream A level 2 etc. If you could come up with something I would really appreciate it!

Range A1-X1000

(I) - Cols A-M,R,W,X
(A1-4) - Cols N-Q
(B1-2) - Cols S-T
(C1-2) - Cols U-V


(I) | (I) | (I) | (I) | (I) | (I) | (I) | (I) | (I) | (I) | (I) | (I) | (I) | List (A1) | List (A2) | List (A3) | List (A4) | (I) | List (B1) | List (B2) | List (C1) | List (C2) | (I) | (I) |



Its going to be a large database so if you can please set the range as 1000 rows it would be great. If it grows beyond that I'm sure I can jump in and change all the 1000 to 2000 and be covered for another while.

Thanks again! I can't tell you how happy I am with this! If you ever need help with something Australia related let me know

 

[jbeaucaire]

I don't follow anything there. Find some place to upload your sample workbook and provide a link here.

 

[tusharm]

For a solution that works with standard relational tables see
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html

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.

 

[jbeaucaire]

The sample file you posted looks great, though there's a bit of unnecessary redundancy, I think. What's your question?

The name range ADMISION is misspelled.