Data validation - Team Name, Thread and Sub-Thread

[Silver]

Column I has team name, Column J has Thread and Columns K,L,M has Sub-Threads

Have created a dummy data which looks as below



Col I
Col J
Col K
Col L
Col M


abc
poi
lkj
dsa
vcx


abc
uyt
hgf
mnb
qaz


abc
rew


wsx


xyz
qaz
plm
tgb
ztg


xyz
wsx
ijn
rfv
ove


xyz
edc


bes



Few things about the dummy data

For Team abc first 3 data under Col J is its thread

poi from Col J is the thread and first 2 data under Column K is its sub-thread
uyt from Col J is the thread and first 2 data under Column L is its sub-thread
rew from Col J is the thread and first 3 data under Column M is its sub-thread

Same is applicable for Team xyz but its Thread and Sub-Threads will be different and
follows the above pattern.

3 Columns will be maintained namely
Column D will be Team
Column E will be Thread
Column F will be Sub-Thread

Data validation should do as below

when abc is selected from Column D, Column E dropdown should reflect
first 3 data of Column J.

when poi is selected from Column E, column F dropdown should reflect
first 2 data of Col K.

When team name xyz is selected above mentioned steps should work for xyz

Note :

Since more team will be added formula should be able to pick-up new additions

Is this possible...

Have attached sample sheet with above mentioned template

[Yongle]

1) Create valid lists of teams, thread and subthread (with header in row1)
2) Name each list (using (formula tab) "Create From Selection", and "Create values from top row")
(By creating tables for each list ( using Insert "Table"), the range becomes dynamic - and so you can keep adding new teams, threads..etc)

3)Then create data validation table. This can also be made dynamic, allowing you to add values without adding the data validation each time, if this is created as a "Table"

What to use for data validation
If team is in ColumnA, and Thread in ColumnB, and Sub-thread in ColumnC
The data validation is always "LIST", and the source will vary with the column
ColumnA : Source = "TEAM"
ColumnB : Source =INDIRECT(A2)
ColumnC : Source =INDIRECT(B2)
etc

Indirect will look to the range name = value of the cell, and provide that list as the valid entries

See attached workbook. I have only put one value against each team to test it works, just add a new line to the table, which will expand automatically

As you can see I have created an additional dummy team, and included it under the Team list, but given it no threads or subthreads. So can add it in first column, but cannot do anything else until valid threads created.

[Silver]

Creating Table IDEA was Awesome as its Dynamic and allows me to add more.

Thanks to YOU... I learned something new

GOD Bless YOU

[Yongle]

We all learn from each other. That is the fun of the forum
Most people do not understand the power of tables. I use them all the time.
- formulas do not need copying down a column any more. Create one row and everything is copied for you.
- (as in your example) create a one column table and it becomes a dynamic range
etc etc