Dynamic lists, the easy way

There are several ways to use the Data Validation option  to generate lists to select values when filling out tables. One of the earlier “recipes” was about how to create dynamic lists. With the advent of the TRIMRANGE function, this can now be done in an even simpler way, without having to use a Name Manager and write complicated formulas.

When we want to select a value from a list in a cell, we will use the Data Validation option  to enable this feature, and indicate the range of cells from where it is populated.

This is a legitimate way to select a range of these values, but the problem is if it changes. By adding or removing rows, we run the risk of “blank” values appearing in the list, which looks rather sloppy. This is corrected by implementation of a dynamic list, but creating such lists is complicated for inexperienced users, so you will now see how, with the help of functions that return dynamic ranges, you can create one such list.

In cell D1, we will write a formula to extract the unique values from column C and sort them in ascending order. Let’s write the formula:

=SORT(UNIQUE(C:C))

We get the desired values, but also a zero at the end of the range.

Let’s correct the formula using the TRIMRANGE function:

=TRIMRANGE(SORT(UNIQUE(C:C)))

The result is a dynamic list.

Now we just need to create a rule using the Data Validation option. In the Source field, you need to type the formula =D1#.

The value in cell A1 is now selected using a dynamically generated list.

If we now, for example, delete the blue value from column C, you will see that the list in column D will be automatically updated, and therefore the sheet in cell A1 on the basis of which the color selection is made. Similarly, if we added the name of a new color to column C, it would appear in the list.