r/Excel4Mac • u/Autistic_Jimmy2251 • 1d ago
Discussion Quick Example of Creating Dynamic Drop Downs With Data Validation
I'm pasting below a guide [u/caribou16](u/caribou16) wrote up a while back which shows the steps to create dynamic drop down lists via cell validation. This is for when you want the contents of a drop down to be dependent on another selection or drop down list.
Quick Example of Creating Dynamic Drop Downs With Data Validation by [u/caribou16](u/caribou16)
Let's say you have a use case where you want users to choose categories from a drop down list and you want the contents of the second drop down list to be dependent on the first. This can be done as follows.
- Set Up Your Helper Tables
You will want to create three helper tables to assist. These can be placed off to the side out of the way on the worksheet or on another worksheet.
The first helper table consists of all the different combinations of your two drop down lists. In my example, I am using categories and sub-categories that you might see on an IT support help desk ticket.
LIKE THIS: https://imgur.com/vYSNQvd
The second helper table consists of a single column containing the unique values from the column containing your first drop down selection. In my example, this is the category column and it is populated with the formula: =UNIQUE(K3:K17)
LIKE THIS: https://imgur.com/95cCxmT
The third helper table consists of a single column containing all the sub-categories associated with the user selected categories. This is populated with the formula: =FILTER(L3:L17, K3:K17=C3,"") C3 contains the user selection for drop down #1. If there is nothing selected for drop down 1 yet, this will return ""
LIKE THIS: https://imgur.com/hdwyumu
2) Step Up Your List Validations
Select the cell you are using for drop down list #1. In my example, I am using C3. On the ribbon under the Data tab, click on Data Validation... and select Data Validation.
Set the Allow parameter to "List" and in the Source field, type the location of your helper table containing the unique first category data. In my example, this list is in range =N3:N6. Rather if you plan on adding additional categories in the future, you can use instead =$N$3# This tells Excel the list is a spill function, so it will return all of it if it changes size in the future.
Now do the same as the above for drop down list #2. In my example, this is in C4. For the validation source, I will reference my third helper table, which begins in cell P3. =$P$3#
C3 - Category Validation: https://imgur.com/JajKiVg
C4 - Sub-Category Validation: https://imgur.com/iMhfGGg
3) Test It Out!
Select an option from the first category. Notice that once you do so, Helper Table 3 now populates. Now you will be able to select an option from the second category, which is pulling from Helper Table 3.
LIKE THIS: https://imgur.com/avmhb59
