So far, you have been able to read several “recipes” on this blog which were about Data Validation and lists. Office365 (Microsoft365) subscription users have access to dynamic array functions that easily handle variable-length lists. But what should users with an older version of Excel do? In the text that follows, you will learn how to solve this problem with the help of the named ranges, OFFSET and COUNTA functions.
Lists, as you know, are created using the Data Validation option which we access from the Data ribbon. First you need to select the cell in which we offer the user to enter values by choosing them from the list. Then we run the Data Validation option, in the Allow field we select the option List, and in the Source field we enter the range of cells from which the values that fill the list will be taken. It is allowed to enter specific values, the range of cells (row or column) where the data is located or the named range. If we enter the formula:
that should be group of cells from which a list will be populated.
What if we need to add a new member to the list? Under normal circumstances, we will change the data validation rule, but if it has already been applied to a large scope of cells, this can be a daunting task. Therefore, we need dynamic lists where, if we add or remove a member of the list, it is automatically updated. The limitation of the Source field, when defining validation rules, is that you cannot use a formula here; you must enter a fixed range address. To overcome this limitation, we will enter the name of the named range in the field, and in the range itself we will create a dynamic content with the help of a formula.
Use the shortcut CTRL + F3 to enter the Name Manager. Click on Add to add a new name. In the field for defining the range we will enter the formula:
Formula says that Excel should, starting from cell G3, select as many cells as there are in total in column G. We used the COUNTA function to count cells that do not contain numbers. The limitation of this rule is that nothing but a list of values must exist in the G column. If the list has a header, we will reduce the count result by 1.
Then, we enter the Data Validation rule as a name of newly created name range.
The list is now dynamic, let’s check how it works. Let’s enter the name “Marko” in field G6. If you open the list you will see that it has been automatically added to the bottom, without the need to change anything withing the Data Validation option.