Creating dynamic dropdown lists

Overview

A not uncommon question is how to create reports that have filters that dynamic update based on selections in other filter dropdown lists.

Consider an organization that is set up as a tree structure such as the below:

In this example, each Department may have multiple Sub-departments, each Sub-department might have multiple Programs, and each Program is assigned a General Ledger subaccount.

We may want to report on an entire Department... or limit a report to just a single Program within that Department.

Let's take a look at how this might be accomplished.

How-To

We will want to set up our report with pulldowns showing the available values for each of our categories - and determine the appropriate subaccount(s) to use, based on the value of each category:

We can then use the subaccount filter as part of Velixo GL functions to return the data we need.

1. Create a List of "Units"

Since Excel is really good a working with lists of data, let's make a list showing each Department along with its Sub-departments and Programs - along with those all-important GL subaccounts:

We'll refer to this as our "Units" list.

By adding an "ALL" option to each Department (and/or Sub-department), we can combine all of the individual GL subaccounts that are associated with each unit.

For example, you can see that that subaccount list for Community Based Services - All - All (3????;4????) encompasses all of the subaccounts for each of the possible Sub-departments and Programs under that Department.

Now that we have this list, let's build the report to use it.

2. Create a Lookup List of Departments

We can start our report by using some simple Excel functionality to create a list of the Department names:

The Excel functions look at the list of Departments (column A in our Units list) and show each of them only once.

Next, let's build a pulldown list that allows the viewer to select the desired Department.

We will create an area where the viewer can specify the Department, and then we will use Excel's Data Validation took (on the Data ribbon):

On the Data Validation dialog, we set the cell to display a list, and the source for that list is whatever is specified in cell K3:

(note that we used the # prefix after the cell reference, so that the size of our list can shrink and grow as the number of Departments shown in column K shrinks and grows).

Once we press OK, we can now select from our list:

3. Create a Lookup List of Sub-departments

Next, we need to build a list of sub-departments that belong to the selected Department.

This function is similar to what we used earlier to return the Department names. This one, however, only retrieves those sub-departments which match up with the Department listed in our dropdown in cell B2.

Next, we can again use Excel's Data Validation feature to create a dropdown for the Sub-departments (pointed to the list we just created in cell L3):

4. Create a Lookup List of Programs

Next we can use the same techniques we applied for Sub-departments to create a list of Programs that correspond to both the selected Department and Sub-department...

... and create the dropdown with Data Validation:

5. Lookup our GL Subaccounts

We are now ready to use all the information that has been gathered to lookup the GL subaccount(s) that we need to use.

We can employ the Excel 365 XLOOKUP function to do this:

=XLOOKUP(B2&B3&B4,Units!A2:A999&Units!B2:B9999&Units!C2:C9999,Units!D2:D9999):

6. Add a Macro to make things work even better

By adding an Excel macro to automatically reset the other dropdowns whenever there is a change to a higher dropdown, we can ensure that the settings are always valid:

We now have a working basis for our report and can build our Velixo functions to use the determined GL subaccounts.