How To Create A Dynamic Range For A Data Validation List With OFFSET In Excel

In this tutorial we are going to use a named range that incorporates the OFFSET and COUNTA functions to create a dynamic range for our Data Validation drop down list in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a Data Validation drop down list that uses the data in column “H”:

71916-5

If I delete or add to the list in column “H”, the Data Validation drop down automatically adjusts:

71916-6

The key is using the following formula as a Named Range:

71916-7

Here we are using the OFFSET function along with the COUNTA function to create the dynamic range for our formula. The OFFSET function “returns a reference to a range that is a given number of rows and columns from a given reference”. The syntax for OFFSET is:

=OFFSET(reference, rows, cols, [height], [width])

Notice that the height and width are optional. By default they will assume one row high and one column wide. We are going to use the COUNTA function to calculate the height, but leave the width to default to one. Our formula is:

=OFFSET(Sheet1!$H$1,0,0,COUNTA(Sheet1!$H$1:$H$100))

I could have also just used H:H as the range for the COUNTA function. Either way will work just fine.

So here is how the formula works:

The OFFSET function will use cell H1 as the starting point, then go down zero rows and over zero columns. So we are still at cell H1. Next, it will use the COUNTA function to determine how “high” or how many rows will be in our range. Since it is counting the number of non-blank cells in column “H”, that will give us the correct number of rows. And since we left off the “width” option, that range will default to one column wide.

The Data Validation drop down list just references the name “Names” as the source of the list:

71916-8

So, each time we modify the list, the COUNTA function will adjust automatically. In the video I walk through creating the same process for a Data Validation drop down list in cell J2 using the data in column L. Take a look and see how the process works.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply