Excel can be a good tool for filling questionnaires and for this purpose are often used structured tables where we keep the results. When the result is one of a few, in advance known, values the input is done by selecting it from the list. Sometimes there is a need to, after choosing value in the first list, affect the items that appear in the second. For example, choosing a category affects the display of related subcategories. How can we achieve this?
To select a category, we need to create the column with all the necessary category names. With the help of Data Validation, we will be able to choose the appropriate values from the list.
Since by selecting a category we need to display a subcategory list, we’ll add three columns: SC1, SC2 and SC3. Here you need to enter items that designate subcategories. The lists we create with the Data Validation option always look at cell range from which values are taken, and our main task is to create one such range list, which will change depending on the selected category. For this purpose, we will create a formula that uses arrays:
After entering the formula it is necessary to confirm it by clicking on CTRL + SHIFT + ENTER, otherwise Excel will report an error. Next, by copying the formula we fill out the range of values that will be retrieved in the list of subcategories. In cells where Excel finds the values it will display them, otherwise it returns 0. So we should add another formula that changes all „zeros“ to „blank“ characters:
Now it’s only necessary to use Data Validation to select a subcategory, which is done by specifying the range in which the calculated values are located.
This solution has one drawback, that is, the list of subcategories always has a fixed number of “slots”. Since 2019 Excel brings new functions that support dynamic arrays which can be used to overcome this problem. But that will be a subject in some of the following „recipes“ …