Can 1st Column Data validation (list) control 2nd Column vlaue list?

[ansvk1]

Hi there,
I need help with the data validation set up. I have 3 tables.
Table1 name=activities (containing 2 data points, being used as a dropdown list using datavalidation on col D)
Table2 name=SubActivities of activity1 (containing 2 data points, being used as a dropdown list with datavalidation on colE)
Table3 name=SubActivities of activity2 (containing 2 data points, being used as a dropdown list with datavalidation on colE)

Now my question is, based on data entered in colD (could be activity1 or activity2 from list in table1), how can we control data entry in colE, to show its related list (table2 items only or table3 items only)?

In other words, dropdown list in colE should show only relevant SubActivities of 1 only or 2 only in its dropdown list depending on what is entered in col D. Is it even possible to make excel do datavalidatiuon lists work this way?

your help is greatly appreciated!
Kris

 

[Sam_ExcelVault]

Hi Kris

You’ll be pleased to hear this is possible! Essentially, your 2 sub-activity lists need to be named the same as the options from your first list, so in your case Activity 1 & Activity 2.

Then, the data validation formula for the 2nd list should read:

=Indirect(D1)

Substitute D1 for whichever cell you have your first drop down list in. The formula will then pick up the value from that cell and use it as the reference for the name of your second list, which will vary depending on the selection of your first list!

 

[ansvk1]

Thank you!! worked like magic

 

[Sam_ExcelVault]

You’re welcome!