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ī. Therefore the named range expands when you add a value and contracts when you delete a value.Ī. 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.

The height in number of rows is COUNTA(List!$B:$B)ĬOUNTA(List!$B:$B) ,counts the number of values that are not empty in column B.

The number of columns you want to move from the starting point is 0.

The number of rows you want to move from the starting point is 0.

$B$2 refers to the reference or starting point.

Here is a breakdown of what the formula represents: Enter =OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B),1) under the Refers to text box Data Validation is used for restricting what type of data should or can be entered into a range.Ĭ. The COUNTA function counts the number of cells that are not empty in a range. We learnt in our previous tip that the OFFSET function is used for creating dynamic data ranges. 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.

Excel drop down list