How To Create An Automatically Updated Drop Down List Using OFFSET in Microsoft Excel

In this article, you will learn how to create an automatically updated drop down list.

Initially, it can take some time to create drop down list but it can save time in the longer run.

The dynamic drop down list will be helpful for the

end user to select the item from the drop down list that belongs to the first list.

In this article, we will use OFFSET & COUNTA functions to make a dynamic drop down list.

Let us take an example:

• We have Fruits list

• Click on Formulas tab
• From Defined Names group, click on Name Manager or press CTRL + F3 shortcut

• The Name Manager window will be open

• Click on New; Enter Fruits in Name box
• Enter the formula in Refers to box as =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

• Where sheet1 is the sheet name
• A is the column where the items of the drop down list are located
• $A$1 is the cell containing the first item of the list.

• The main benefit of using dynamic dropdown lists is that you do not have to change the reference to the named range every time after editing the source list. You can modify the data, and all of the cells containing the current Data Validation list will get changed automatically.

In this way, you can create automatically updated drop down list.