Excel/Data validation - Team Name, Thread and Sub-Thread
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
Since more team will be added formula should be able to pick-up new additions
Is this possible...
The most common approach is to name each range of cells that will be used in the dropdown
So for team abc you would create a range of cells named abc that contained the threads for abc
then say team is in D1, the definition for the list for e1 would be =Indirect(A1)
then you would need to name a range of cells using each of the Thread names. Generally these names need to be vertical cells. (cells in a single column).
If you want an example, send a worksheet to email@example.com