UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Cascading Dropdown Lists (Non-VBA)    
Cascading Dropdown Lists (Non-VBA)

Using Pivot Tables to Control Cascading Dropdown Lists (Non VBA)

A cascading dropdown list is a dropdown list where the choices available to the dropdown is limited by a choice made in a previous dropdown list.

There are several methods for accomplishing cascading dropdown lists. The method shown here takes advantage of Pivot Tables to group data and display the number of elements in a grouping.

The example presented here is one where the user selects a region, the selected region determines which states can be selected and the selected state determines which cities can be selected.

The regions, states and cities are maintained in an Excel Table, and several pivot tables are built on this data on an auxiliary sheet.

The Data Table looks like the following:

image:Sample Table.jpg

Each city is associated with a state and each state is associated with a region. Regions may have multiple states and a state may have multiple cities.

The counter column contains the formula =1/COUNTIF([State],B2). What this fromula does is force the count of states to be 1 for each state. For example, Florida is to be counted only once when it appears in state pivot table.

The same logic can be extended to cascade down another level to an office or store in a city. In this case, there would be two count columns: one for state, and another for city. The logic can be used to drill down indefinitely.

The dropdown lists are managed using the OFFSET command, and for the sake of clarity use helper cells.

First, the highest level pivot table (Region) is created.

image:Pivot Region.JPG

Next, the State Pivot Table is created.

image:Pivot State2.JPG

Finally, the City Pivot Table is created.

image:Pivot City.JPG

The column totals and row totals for the pivot tables should be turned off.

Now that the pivot tables are in place, the named ranges are created in the same order.

Region_List is a basic OFFSET command:

=OFFSET('Dropdown Lists'!$A$4,0,0,COUNTA('Dropdown Lists'!$A:$A)-1,1)

The Region dropdown is established in Cell J2 with a list type data validation set =Region_List.

Once a Region is selected, it can be used to find where it is in the State Pivot table. The helper cells in Cells M2 and P2 do this. The formulas are:

Cell M2: The row on which the selected region appears in the State Pivot Table =MATCH(J2,C:C,0)
Cell P2: The number of states in the region =OFFSET(C1,M2-1,1)

These helper cells are used in the OFFSET formula for State_List

=OFFSET('Dropdown Lists'!$C$1,'Dropdown Lists'!$M$2,0,'Dropdown Lists'!$P$2,1)

In a similar fashion, the helper cells and OFFSET formula for the cities are:

Cell M4: The row on which the selected state appears in the City Pivot Table =MATCH(J4,F:F,0)
Cell P4: The number of states in the region =OFFSET(F1,M4-1,1)
=OFFSET('Dropdown Lists'!$F$1,'Dropdown Lists'!$M$4,0,'Dropdown Lists'!$P$4,1)

This method takes advantage of the pivot table “knowing” how many sub-items it has. No complicated formulas are needed to find the boundaries of the range.

Donwload the sample table here: Media:Region_Dropdown.zip

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 6,394 times.  This page was last modified 13:09, 18 July 2013 by dflak. Contributions by Jack Leach  Disclaimers