You are here:

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

Note :

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

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.