Dependent Drop-downWhile working with Excel, we all have created or gone through Combo box. It is a dropdown that gives you plenty of options from where you are supposed to choose. Excel dropdown is a valuable feature commonly used when creating a user form or Excel dashboard. However, you might often require adding more than one dropdown list in your Excel worksheet, wherein the option in the second dropdown is only available if the preferred selection is made in the first dropdown list. These are called dependent dropdown combo boxes. This tutorial will briefly cover all the details related to dependent combo boxes, how it is created, the advantages and many more... What is Dependent Combo Box?For instance, in the below example, you can check We have created two drop-down where drop-down 2 is dependent on drop-down 1. There are plenty of options in Drop Down 1, but when I selected the option 'Fruits' in Drop Down 1, only we are shown the fruit names in drop-down 2. Likewise, if we chose Vegetables in Drop Down 1, the vegetable names list is displayed in Drop Down 2. So, here, dop-down 2 is a Dependent Drop-down. Steps to Create a Dependent Drop-Down ListBelow given are the step-by-step implementation to create a dependent drop-down list in Excel worksheet:
NOTE: Following the above step will quickly create 2 named ranges ('Fruits' and 'Vegetable'). The Fruit named range will contain the list of all fruits, whereas the named Vegetable range will have the list of all the vegetables.
Explanation- The conditional drop-down list (present in the cell H2) contains the formula =INDIRECT(G2). It represents that when we select 'Fruits' in cell D4, the dependent drop-down list in E3 implies to the named range 'fruits' (with the help of the INDIRECT Function) and hence returns all the items in that category. Remove the items of Dependent Drop-Down List in Excel AutomaticallyAfter creating the parent and the child drop-down, any modification made in the parent drop-down would not apply to the child drop-down. The dependent drop-down list would not change and, therefore, bit would lead to a wrong entry in your Excel worksheet. For instance, if you choose the 'Fruits' category and choose Apple from the dependent drop-down, then you return again to the main category and this time, you decide to change the main category to 'Vegetables', but this time the dependent drop-down will continue to show Apple as the item. A problem right! But here is the crack. Using Excel VBA, you can easily ensure that the contents of the dependent drop-down list should reset automatically whenever there is any change in the primary drop-down list. Follow the steps to quickly remove the items of dependent drop-down list in Excel within a single click using VBA code:
Code:
As a result, now, whenever you make any changes in the primary drop down list, the VBA code will shoot up and it will remove all the elements from the dependent drop-down list. (refer to the below image) Next TopicLine Break in Excel |