Dynamic categories

One of the earlier “recipes” mentioned categories and subcategories. If you remember, we wanted to make it possible to automatically create a list with a selection of subcategories by selecting a category from the list generated with Data Validation. We could have done this by applying a few tricks, but the list, in addition to the necessary values, contained “empty slots”. This can be easily avoided today with the help of dynamic arrays.

First of all we have created a test data table containing two columns: CATEGORY and SUBCATEGORY. Above it, we have specified fields whose values ​​will be selected based on the parameters defined using the Data Validation option.

To generate a list whose members will be in the category selection, we will use the UNIQUE function. Let’s write the formula:

= UNIQUE (TT[CATEGORY])

It returns unique values ​​from the CATEGORY column. Now all you need to do is set up Data Validation. It is necessary to specify that values ​​are selected from the list, and as a formula we specify a value:

= $D$6#

The list should indicate the first value in the array, which is cell D6, and to emphasize that all values ​​of the array should be retrieved after the cell address we’ll add # character.

To create a list that contains subcategories, we will write the formula:

= FILTER (TT[SUBCATEGORY], TT[CATEGORY]=A2)

As a result this formula returns a list of values ​​that match the filter based on cell A2, i.e. previously selected category. In cell B2 we should further enter Data Validation rules. Values should be populated from a list, and as a criterion should enter the formula:

= $E$6#

In this way, we have ensured that by selecting a category, the list of subcategories will be automatically populated. And since we’re using dynamic arrays it is possible to easily add an additional category or subcategory – they will automatically become part of the first or second list.