For example, if you select the Fruits as the category and then select Apple as the item, and then go back and change the category to Vegetables, the dependent drop down would continue to show Apple as the item.It shows a list of items as a drop down in a cell, and the user can make a selection from the drop down.This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells.
Below is an example of an Excel drop down list: In the above example, I have used the items in A2:A6 to create a drop-down in C3. Creating Dropdown List In Ecel 2016 How To Create AnRead: Here is a detailed guide on how to create an Excel Drop Down List. Sometimes, however, you may want to use more than one drop-down list in Excel such that the items available in a second drop-down list are dependent on the selection made in the first drop-down list. Below is an example of what I mean by a dependent drop-down list in Excel: You can see that the options in Drop Down 2 depend on the selection made in Drop Down 1. This is called a conditional or dependent drop down list in Excel. Creating a Dependent Drop Down List in Excel Here are the steps to create a dependent drop down list in Excel: Select the cell where you want the first (main) drop down list. In the data validation dialog box, within the settings tab, select List. In Source field, specify the range that contains the items that are to be shown in the first drop down list. Click OK. This will create the Drop Down 1. Go to Formulas Defined Names Create from Selection (or you can use the keyboard shortcut Control Shift F3). In the Create Named from Selection dialog box, check the Top row option and uncheck all the others. Fruits named range refers to all the fruits in the list and Vegetables named range refers to all the vegetables in the list. In the Data Validation dialog box, within the setting tab, make sure List in selected. Here, D3 is the cell that contains the main drop down. Click OK. Now, when you make the selection in Drop Down 1, the options listed in Drop Down List 2 would automatically update. Creating Dropdown List In Ecel 2016 Download The ExampleDownload the Example File How does this work The conditional drop down list (in cell E3) refers to INDIRECT(D3). This means that when you select Fruits in cell D3, the drop down list in E3 refers to the named range Fruits (through the INDIRECT function ) and hence lists all the items in that category. Important Note: If the main category is more than one word (for example, Seasonal Fruits instead of Fruits), then you need to use the formula INDIRECT(SUBSTITUTE(D3,,)), instead of the simple INDIRECT function shown above. The reason for this is that Excel does not allow spaces in named ranges. So when you create a named range using more than one word, Excel automatically inserts an underscore in between words. For example, when you create a named range with Seasonal Fruits, it will be named SeasonFruits in the backend. Using the SUBSTITUTE function within the INDIRECT function makes sure that spaces are converted into underscores. ResetClear Contents of Dependent Drop Down List Automatically When you have made the selection and then you change the parent drop down, the dependent drop down list would not change and would, therefore, be a wrong entry.
0 Comments
Leave a Reply. |