two columns and dependent validation

I have the following situation.

Sheet2

row ColA --- ColB
1 --- A --- 1
2 --- A --- 2
3 --- C --- 3
4 --- D --- 10
5 --- E --- 20
6 --- E --- 30

Sheet1

I need to create two drop-down menu-Validation List. The second depends on the first, because the same character in the first has two choices
These are the results to be obtained



row --- Validation1---Validation2 dependent of 1
1 --- A --- 1 or 2
2 --- E --- 20 or 30

I'm looking for example at the forum but did not find such an example. I also looked at w.contextures.com

Please, How?
No Macro please

Hello Dumy,

if you've looked at contextures.com, you should have seen how it's done. The tutorial is in

http://www.contextures.com/xlDataVal02.html

If you get stuck with that, please upload your file and tell us what does not work for you.

cheers,

i cant do it either . it requires a list called C which excel wont accept as a valid name for a list but i can with a slight renaming

Your data is not laid out correctly. Follow the tutorial.

In Excel 2003 the OP can accomplish what he wants with the layout he has. As long as the colA range is sorted ascending, then on Sheet1 this DV formula will do the trick:

Allow: List
Source: =INDEX(colB, MATCH($A2, colA, 0)):INDEX(colB, MATCH(A2&"zzz", colA, 1))

Originally Posted by Dumy

Can this problem two dependent validation lists, to solve the same manner using a similar formula, even if the data are not sorted ascending, the data are scattered?

No, just put the data in willy nilly as you will, then sort it before you go and start using the data validated drop downs.

If for some reason this has become impossible, you would have to create a whole 'nother table that is analyzing your data rows one by one and creating a sorted copy of the whole thing...a lot of plumbing I hope isn't necessary.