Dependent list in data validation - more than 2 columns

[YY san.]

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!

 

[Sheeloo]

you need to use the method described at
http://www.contextures.com/xlDataVal02.html

Essentially Create following NAMES
ColA referring to TaskA, TaskB,... and other entries you want in the
dropdown...
Then create a NAME each for TaskA, TaskB....
Task A should list the options you want to see if Task is selected for
ColA... Similary for TaskB,...
Now for the each possible value which can be selected in Col B, create NAMES
with options...

Now for dropdown of Col A choose LIST and formula
=ColA
For Col B (with first cell selected as A2)choose LIST and formula as
=INDIRECT(A2)

For Col C choose LIST and formula as
=INDIRECT(B2)

For your example the NAME
ColA will refer to TaskA, TaskB
TaskA to BAT, PRJ
TaskB to BAU
BAT to Z101
PRJ to Z001, Z002
BAU to Z101, Y403, Y407, and Y412


Hope it is slighlty more clear... Download the sample from the URL above ...
and spend some time with it to play around...

 

[T. Valko]

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 C22 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.

 

[YY san.]

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.

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,

 

[YY san.]

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]

Well, the setup of the table and the "crafting" of the defined names can get
messy especially if there are lots of possible selections and lots of
dependent variables.

You could create a "flat database style" table and then use formulas instead
of a bunch of defined names. Sort of like this:

TaskA...BAU...Z101
TaskA...PRJ...Z002...Z003
TaskB...BAU...Y403...Y407...Y412
TaskB...AAA...1111...2222
TaskB...BBB...1000...2000
TaskC...DDD...5555