Limited choices

[Werner Peerboom]

hallo, a little brainteaser for the weekend.

I've run in to this problem, it envoled limited choices.

Short discription:
Basically the content of the second list is alter by the choice made in the fist list.

Long discription:
In the first list there are 13 choices, the choices are: 1, 2, 3, 4, A, B, C, Aa, Ab, Ba, Bb, Ca and Cb.

Choices 1, 2, 3 and 4 are related to EN 10308 and EN 10228.
Choices A, B and C are related to SEP 1920
Choices Aa, Ab, Ba, Bb, Ca and Cb are related to SEP 1921

When I select A of Bb the field next to it shows SEP 1920 of SEP 1921, this is the easy part. But as you can see the numbers have two opties, there either related to EN 10308 or EN 10228. So when I select a number I need to have a second list, not showing me one result but both, and I want to select one of these choices.

I have inserted the excel model. limited choices.xls
I hope this is clear enough.

Best regards,
Werner Peerboom

[MarvinP]

Hi Werner and welcome to the forum,

The topic you seek is called Cascading Dropdowns. You can have them in Lists, Combo Boxes or Data Validation. I think http://www.bluepecan.co.uk/excel_tra...alidation.html will explain it for you.

[Whizbang]

Here is a recent thread that shows the INDEX() approach, rather than the INDIRECT() approach.

http://www.excelforum.com/excel-gene...dropdowns.html

Here is a link explaining the power of the Index function:
http://www.excelhero.com/blog/2011/0...ing-index.html

[Werner Peerboom]

I shall try this out a.s.a.p.

Thanks in advance.