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

img2

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

img3

 

  • The Name Manager window will be open

img4

 

  • 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)

img5

 

  • 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.

img6

 

  • 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.



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>