How to create cascading dropdowns

How to create cascading dropdowns
Cascading dropdown lists also called the dependent dropdown lists. Its called cascading dropdown because there another dropdown depend on the selection of the first dropdown
Here in example describe how to create cascading Dropdown list 
How to create cascading dropdowns

The category is the first list to be created and depend on that there is another table which contains the category with type 
1. I am going to create a dropdown list of category
2. Then select the category next dropdown will show type which is related to the category
3. I am here also show the data is valid or not 
Create first category dropdown list
Select B6 and go to the data tab
How to create cascading dropdowns



Go to data Validation and on dropdown select data validation 

Select list on validation criteria ]
How to create cascading dropdowns


And select the category in list source and click on ok 
How to create cascading dropdowns

Now our category dropdown list will be created 


How to create cascading dropdowns


Now we have to go next step for the create the cascading dropdown list, here after category select type should be related to the category, if I choose food type should be rice and grain only. To create same we have to follow same step to data validation after list selection we need to change the formuls
Formula I used to create cascading dropdown list is 
=OFFSET(Lists!$D$1,MATCH(B6,Lists!$D:$D,0)-1,1,COUNTIF(Lists!$D:$D,B6),1)
How to create cascading dropdowns

 
If I need to be validate is validate data is selected or not i added extra two function in G6 and I6
I6 give value either 0 or 1 if 0 appear its invalid and if 1 appear it’s a valid selection
And G6 give the message
In I6 Type following formula
=IF(ISBLANK(B6),1,(COUNTIF(OFFSET(Lists!$D$1,MATCH(B6,Lists!$D:$D,0)-1,1,COUNTIF(Lists!$D:$D,B6),1),E6)))
And in G6 Type 
=IF(H6=0,"<- Invalid Entry - Select Type","") formula

Summary
This is the example for the create cascading dropdown list and I used the following inbuilt function
OFFSET,MATCH,COUNTIF,IF,ISBLANK

0 Comments