Dear all,
Thank you to Sheeloo and Valko for your reply. I have already studied quite
thorough on DataVal02.html and DataVal13.html, and I dont think I can use it.
Because in my actual requirements, Col C is a text field that contains 30
characters long. If I used the DataVal02, my name ranges are going to get
very messy. And also in total, I have about 500 rows. Another reason why I
think I cant use DataVal02 is because Col B values are not unique.
I have tried using DataVal13 which I managed to display BAU,PRJ,PRJ if TaskA
is selected and BAU,BAU,BAU when TaskB is selected. I have a name range
called BAU,PRJ, which I am thinking of using this, to lookup/match against
the ColB results, extract and display distinct value. Can I do that?
Thanks,
"T. Valko" wrote:
You need to create a table that lists all the related items.
...........A..........B..........C..........D
1....TaskA...BAU....Z101..........
2.................PRJ......Z002.....Z003
Give B1:B2 the defined name TaskA
Give C1 the definhed name BAU
Give C2:D2 the defined name PRJ
Then, assuming your 3 drop downs are in X1:Z1
X1 = TaskA
As the source for the drop down in Y1 use: =INDIRECT(X1)
As the source for the drop down in Z1 use: =INDIRECT(Y1)
The hard part to this (time consuming) is building a good efficient
hierarchical table and creating the named ranges.
--
Biff
Microsoft Excel MVP
"YY san." wrote in message
...
Hi,
I wanted to create 3 dependent lists. For example:
Col A Col B Col C
------ ------ ------
TaskA BAU Z101
TaskA PRJ Z002
TaskA PRJ Z003
TaskB BAU Y403
TaskB BAU Y407
TaskB BAU Y412
I need to find out what formula should I put in my data validation in Col
B
so that when TaskA is selected, I will only see "BAU" and "PRJ", and if I
select BAU, only "Z101" will appear in the list (likewise if PRJ is
selected,
Z002 and Z003 will be displayed.
When TaskB is selected, I will only see "BAU" once. I have tried to use
http://www.contextures.com/xlDataVal02.html
http://www.contextures.com/xlDataVal13.html
But cant get it to work. I can only get to the stage where BAU will appear
3
times.
Thank you so much in advance for your help.
Cheers!