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
Last edited by Dumy; 11-15-2010 at 03:59 AM.
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,
OK, This is my file attach, thanksOriginally Posted by teylyn
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
Last edited by martindwilson; 11-14-2010 at 05:49 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
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))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
This is not the answer for me. I have 5000 data in column colA
This is the reason why I did not find my way to http://www.contextures.com
I apologize that I have not mentioned.
Thank you, this is the correct solution for me
This problem and topic is Solved, but I have a new problem
I apologize if I'm wrong and if I should open a new topic (I don't know); (moderator can switch if need be)
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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks