List validation

[Brendan]

Hey guys.

Not sure how to word this properly, but I'll try my best.

I know how to validate a list, so a drop down box appears and only the options that have been defined in that name are able to be selected.

What I am now interested in is having two drop down boxes that work together.

So for example I have two lists.
In the first list I have :
Anna
Jon
Joe

Any of these names can be selected in cell A1.

In cell B1, I want it to look at cell A1 and return only specific values relating to those names.

So if we have 9 fruit in the second list.
Grapes
Apples
Bananas
Pears
Oranges
Kiwifruit
Grapefruit
Peach
Apricot

Now if I select Anna - She only likes Grapes Apples and Peaches, so I only want those items to be the only options when I select Anna in A1 from my drop down validation list.

Joe only likes Oranges, Kiwifruit, Grapefruit and Peaches, so only those items will be my options when I have selected Joes name in cell A1.

Jon likes Bananas, pears and Apricot - so when his name is selected in A1, B2 will only display his favourite fruits ..

Does this make much sense at all?

 

[lenze]

The technique you are referring to is called "Dependent Lists". A search should turn up several examples, but first check out Aladin's Hall of Fame entry here
http://www.mrexcel.com/board2/viewtopic.php?t=53213

lenze

 

[Brendan]

Thanks heaps lenze