Click Kutools > Drop-down List > Searchable Drop-down List > Enable Searchable Drop-down List. Therefore a data validation list has been created, allowing flexibility when selecting data. Please do as follows to create a searchable drop-down list with the Searchable drop-down List feature.
The number of months has increased to 12īased 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. Select the drop down list in cell Q2 in the dashboard worksheet. Select the List worksheet and increase the number of months to December(12)ĥ. Select the drop down list in cell Q2.There are only six months in the listĤ. Place the cursor in the Source text box and press F3. Select Data Validation under the Data Tabĭ. Select the Dashboard worksheet, then select cell N2ī.
For this, all you have to do, create a third drop down list which is dependent on the selection of the second drop down list. But sometimes, we need to create a list with three level dependency. Therefore the named range expands when you add a value and contracts when you delete a value.Ī. In above example, you have created a two level dependent drop down list. The number of values that are not empty will decrease when you delete a value from the range. When you add a value to the range, the number of values that are not empty will increase.
However, in this tip we show you how to create a dynamic drop down list by using Data Validation, OFFSET and COUNTA functions. We have written tips on how to create a static and dynamic drop down list using and the Table option. 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. Let’s say you have a list where you are likely to add or remove values, a dynamic drop down would be the best option to select data. A dynamic drop down list in Microsoft® Excel® is a convenient way of selecting data without making changes to the source.