Recent

6/recent/ticker-posts

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

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

Go to data Validation and on dropdown select data validation

Select list on validation criteria ]

And select the category in list source and click on ok

Now our category dropdown list will be created

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)

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