Dependent Lists

[jazchua]

Can some kind soul help me?

Here is my requirement. The thrid list is dependent on choices of the first and second list.

Class A --- choose Maths ------choose Further Maths or General Maths
Class A ---- choose English ----- Choose Writing or Spelling or Converstaion

Class B --- Choose Science --- choose Biology
Class B ----Choose German Lang ---- choose German converstation or German writing

Class C --- Choose Tuba -----Choose Reading of music notes
Class C --- Choose Flute -----same choice above i.e. Reading of music notes

I am able to use validation list for the first drop down list for Class i.e.Define name: Class and the second drop down list for Subjects i,e, =INDIRECT( )

Can please help me with the third drop down menu?

I have used the entire afternoon looking over the posts and the closest I have is http://www.contextures.com/xlDataVal02.html

However, I am still unclear about how to make my thrid list adn where to place the thrid list.

By the way, I am using Excel 2007.
Please use simple language. I am very new to all the intricacies of excel and have been driven by work requirments to learn baout pivot tables and validation within 2 days.


Thanks in advance.

 

[xld]

Dependent list are called that because they are dependent on ecah other. It is very difficult to see any dependency in yours, they seem to be totally self-standing, lists of class options.

Are you sure that you just don't want to allow double-picking?

 

[jazchua]

What I mean is if a student chooses class A, the drop down menu shows only Maths and English for the second list. For the 3rd list, based on what he chooses i.e. Maths, the drop down menu shows only Further Maths or General Maths. IF he chooses, English, the drop down menu shows writing, Spelling, Conversation.

Also under List 1, if a student chooses Class C, the drop down menu shows Flute and Tuba. Should he choose Flute or tuba, then the drop down menu should only show Reading of music notes.

Apologies, I am not sure if this is called dependent list. It is more like I want validation of drop down choices. The result of list 3 is based on the choise of list 2 which is dependent on the choice of list 1. Hope this clarifies.

I am just not clear how to make the third list work.

 

[Dougie1]

Here is my suggested way to set up your lists (works in Excel 2003):

This is a very basic example just to explain. The most important thing is that you name your lists correctly.

The first thing to do is define the values that you would like to appear in the first cell dropdown list. Let’s just say that we want the list values to be A, B & C - so just type A, B & C into cells A1:A3.

Now highlight cells A1:A3 and in the Name Box, define the name of the list as Master and hit Enter.

Now go on to create your first set of lists which will be dependent on the selection made in the original list. You have already created a list called Master which contains the values A, B & C.

So go on to define the values for your three new lists. Type 1, 2 & 3 into B1:B3 and define the list as ListA. Type 4, 5 & 6 into C1:C3 and define the list as ListB. Type 7, 8 & 9 into D1:D3 and define the list as ListC.

NOTE : The A in ListA, the B in ListB and the C in ListC refer to the actual contents of the selection made in the cell where the first list is. So were the values in the list named Master equal to 1, 2 & 3 then the secondary list names would have to be List1, List2 & List3


Now go on to create your second set lists which will be dependent on the selection made in the second list. You have already created this second set of lists and called them ListA, ListB and ListC.

So go onto define your values for your nine new lists. Type Apple, Orange, & Pear into E1:E3 and define the list as List1. Type Red, Blue & Green into F1:F3 and define the list as List2. Type Pig, Sheep & Cow into G1:G3 and define the list as List3.

Type Beer, Wine, & Whisky into H1:H3 and define the list as List4. Type Earth, Wind & Fire into I1:I3 and define the list as List5. Type Football, Rugby & Cricket into J1:J3 and define the list as List6.

Type Bread, Cheese, & Eggs into K1:K3 and define the list as List7. Type April, May & June into L1:L3 and define the list as List8. Type Ear, Nose & Throat into M1:M3 and define the list as List9.

Next go the cell where you would like to locate your first dropdown list (let’s put it in N1). From the Data dropdown menu, select Validation. This will bring up the Data Validation options box.

On the Settings tab select List from the Allow menu. This will activate the Source box. You should now type = Master into the Source box and click OK.

Go back to cell N1 and you will see your list has now become active by using the small arrow that appears at the bottom right corner of the cell.


Next, using the same method as above :

In cell O1 set the Data Validation to Allow List, Source =INDIRECT("List"&N1)

In cell P1 set the Data Validation to Allow List, Source =INDIRECT("List"&O1)


So now when you select an item from the first dropdown list, the items in the second dropdown list change accordingly.

Also when you have made your selection from the second dropdown list, the items in the third dropdown list change accordingly.

If you follow this method you should be OK.

Cheers,

D