Back to top If you’re using Excel 2007 or later you can create a drop down from a source range that is located in another worksheet.
Then, the first and second selections are mismatched -- for example, in the screen shot below, Vegetable is the first choice, and Banana is selected in the second column.This is called a conditional or dependent drop down list in Excel.Here are the steps to create a dependent drop down list in Excel: Now, when you make the selection in Drop Down 1, the options listed in Drop Down List 2 would automatically update. – The conditional drop down list (in cell E3) refers to =INDIRECT(D3).The lists (or drop down lists) are a type of data validation used to make sure the users enter a value from a set of options.To create/modify a drop down list: In the Data Validation dialog box click in the validation criteria selection button and select ‘List’: If you’re modifying an existing list, the validation criteria type will be preselected with the existing type.The COUNTA function counts the number of cells that are not empty in a range. When you add a value to the range, the number of values that are not empty will increase. Select the Dashboard worksheet, then select cell N2 b. Data Validation is used for restricting what type of data should or can be entered into a range. The number of values that are not empty will decrease when you delete a value from the range. With a dynamic drop down list, when you delete or add months the list changes to accommodate that action, whereas a normal list does not. Select the drop down list in cell Q2 in the dashboard worksheet.We have written tips on how to create a static and dynamic drop down list using and the Table option. The number of months has increased to 12 Based on the above example, the number of months in the data validation list will decrease if you delete some values from the range B2 to B13 on the list worksheet.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.Now, the drop down in the first column won't work if the Item has been selected in that row.In the previous section, the data validation formula was designed to prevent selections from the first drop down, if the cell to the right contained data.To block changes to the first list, you can change the data validation formula, so the list doesn't work unless the second cell is empty.