three tier conditional drop down menues

[Linda01]

Dear all

I am trying to set up a series of three drop down menues which are conditional upon the choice made in the previous drop down so:-

 

Drop down 1 may have choice 1, 2 & 3

 

Drop down 2 will show values A,B & C if 1 is chosen, L, M & N if 2 is chosen and X, Y & Z if 3 is chosen

 

Drop down 3 will show A1, A2 & A3 if A is chosen, B1, B2 & B3 if B is chosen etc.

 

I have created a table which has a record for each permiatation in it and am generating the value lists using the "include only values from ..." option within the value lists diag box. I am using self referancing tables to generate the relationships.

 

Drop downs 1 & 2 work like a dream, but drop down 3 only ever shows the A, L and X lists no matter if B,C or M,N or X, Y are chosen.

 

I am also trying to display the record ID but again only the first one in each group from drop down 2 is ever shown.

 

I have tried every table combination and relationship that I can think of and failed to sort this

 

All suggestions would be very much appreciated

 

Thanks

 

Linda

[David Head]

I have made up a sample file for you to look at. It does what you want with the value lists. Look at the relationship graph and the value list definitions.

 

In this case, you are not fully normalising the value combinations but that is OK. You could have three related value list tables but that would make the solution more complex than what you see here. However it does have the advantage for adding new values.

 

Anyhow, see what you think.

[Linda01]

David

Thank you so much, I would never have thought of taking the two self referencing tables off the base file seperately, I was trying to do it as a string. It works like a dream. You've made my day

 

Linda

[David Head]

No problems. Paid for your membership already!

[hmonster]

Hi

Im new to the forum and a beginner on excel (but trying to learn).

I am attempting at doing the same conditional drop down menus but Im struggling to get any further than the second tier.

 

Any assistance would be greatly appreciated.

 

Thanks

Mark

[hmonster]

Hi agian

Is it possible to carryout this coinditional formatting with results from 2 previous cells?

For example

Cell A = Primary School

Cell B = Area 1

Cell C = List of Primary schools in Area 1

 

I would be very greatful if you could help.

 

Thank you