Data Validation - Growing List / Dynamic List

Welcome back. In this post, we will talk about one issue I frequently face while using validation and how to overcome that. I am sure many of you may also face this kind of problem but deal with it in boring way. Let’s dive into the problem and find out a solution.

The problem I am talking about is growing number of list items. We know that we create a Named Range to refer to a range and use the names later in validation. If you remember our example on using validation lists, I took 2 country names viz. India, USA and named it as CountryList which refers to range A7:A8.

What if I realised later that I had to take one more country which I entered recently in cell A9? I will have to open the Name Manager and edit the reference to include that one more cell. If your list is dynamic and change like this very frequently, would you like to do this manual task all the time? Just like any other time, Excel is to your rescue. Let’s see how.

Frequently changed list is what I call a Dynamic List. You can make Excel change the reference automatically each time an entry is added to the existing list. There are 2 ways to do this according to me.

Dynamic Named Range by OFFSET function

Select the list items you want and click on Formulas ribbon Define Name. In the New Name dialogue box that opens up, give the name of your choice (of course, you will have to follow naming conventions). In the Refers to field, enter the below formula.

Excel 2003 or earlier versions

=OFFSET(Cascade!$A$7,0,0,COUNTA(Cascade!$A$7:$A$65536))

Excel 2007 or later versions

=OFFSET(Cascade!$A$7,0,0,COUNTA(Cascade!$A$7:$A$1048576))

In the above formula, I assumed that your list is in a sheet named Cascade, list items started from cell A7. You can modify the formula as per your requirement. This validation drop down list items will be automatically updated as you add more items to your list.

Let’s also quickly see what this formula is doing. COUNTA function calculates how many non-blank cells are there in the range starting from A7 till end row of the sheet (65,536 rows in Excel 2003 or lower & 1,048,576 rows in other versions). OFFSET function creates an array of cells from A7 moving down by number of non-blank cells calculated by COUNTA.

Next, go ahead and use the name in validation.

Points to note:

1.  There should not be any blank rows in between the list items.
2. Instead of creating a name and using the name in validation, you can directly enter the formula in the validation source. But as we saw, using names will be less messy in complex situations.

Dynamic Named Range using Tables

If you are using Excel 2007 or later version, you can use this method to achieve dynamic named range. We will be taking help of Excel’s another excellent feature Table here. In our example, we have list of countries in A7:A8 with a heading for this list in A6. Select cells A6:A8 and click on Insert ribbon > Table. Below is what you should see at this point of time.

In the Create Table dialogue box that pops up, make sure A6:A8 is the range mentioned. If not, you can now select the proper range by clicking on that field. Also make sure that the check box “My table has headers” is checked and click OK. Now you can notice the range you selected has changed colours.

Now open the Name Manager. Do you notice there is new member added in the names? By default it will be named as Table1 (or Table2 and so on depending on how many tables you already have in the workbook). If you don’t like the name, you can change it by clicking on Edit in Name Manager.

That’s it. You have created a table which is a dynamic range itself. Notice the range this table refers to. Now go to end of this table (table ends at A8, that’s what we selected for table, right?) i.e. cell A9 and enter any text there. Now open Name Manager and notice the reference. If everything goes good, you should see the reference already changed and new reference will end at cell A9. But remember, you should enter any new items from the very next cell of the end of the table, otherwise your table will not consider the new entry as part of it.

Bingo, you have a dynamic range name which can be used in your validation right away. Go ahead and try it. Let’ me know if you have any doubt or could not get what we are talking about. Catchya later!!