Dependent drop down menus in Excel

[mgodbee]

Need help for a project I am trying to do in Excel. I am trying to do a "dashboard" for work that will display sales based on a myriad of geographical breakdowns. The best way I can think of doing this is through a serious of dropdown menus, 3 to be exact. I want the first dropdown to be the high level groups: division, region, market. Based on the selection above, I want it to populate the 2nd dropdown menu with the choices based on the selection of the first 3, i.e the actual divisions, regions, markets. This will then populate the 3rd drop down menu which will only be stores in the selected region.

I know a minimal amount about VBA. Also, I am wondering if I can populate all of the options from a single Database type list or do I really have do a menu list for each case? The 3rd menu is the one I am worried about it would require me to make a couple of hundred different options for the dropdown box. If I did do it from a list, I still would hav e the issue of duplicate listings in a dropdown wouldn't I? For example, it would list Northern Division 200 times on the dropdown

My last concern is getting the boxes to clear or return to the "ALL" function if someone goes back and clicks on the 1st 2 menus without clearing the 3rd menu (store).

Based on the selections, the sales data will be populated by one master pivot table and using "getpivotdata". I have figured out how to do this part based on an input cell, but don't want to depend on lesser knowledgeable excel users to manually enter the data, hence why I am wanting a dropdown menu.

Any help would be greatly appreciated.

 

[SkipVought]

Hi,

I use MS Query as a rule, to generate dependent lists. The Selection in the primary combobox becomes a criteria for the query, the resultset of which is the rowsource for the dependent combobox.

faq68-5829.

You can use a parameter or make the substitution via VBA in the SQL CommandText parameter of the QueryTable.

Once you ADD a QueryTable to a sheet (a ONE TIME thing that I do manually via Data > Import External Data ...) you can use DebugPrint to display the CommandText....

with sheet1.querytables(1)
  debugprint .commandtext
  debugprint .connection
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mgodbee]

I really don't understand how to do this even though what you are saying makes sense. The pivot table holding all my data is linked to the external data source already though using MS QUERY if that helps any.

 

[electricpete]

Skips approach is probably best as usual. I am not familiar with those query features myself.

The data / validation / list approach can do what you want also:

http://home.comcast.net/~electricpete/eng-tips/ContextSensitiveDataValidation.xls

The first selection of division is based on data validation with simple list.

The second selection of state is a little trickier - the data validation list formula is
=INDIRECT(DivisionSelection)
Where DivisionSelection is named range for the first selection.

The trick is that all of the states in a given division are given in a list with that division name at the top and named ranges assigned based on the division (top row). So indirect(divisionselection) pulls up the list whose name is the division name, which contains all the corresponding states. I probably made it sound complicated, but really very simple.

 

[Gavona]

An alternate approach that I am currently using is based on Data, Validation...List.
Assuming that your dropdowns are in cells A1, A2, A3:

The first drop down would be a straightforward named range.
The second dropdown would use the named range =Indirect(A1)
The Third dropdown would use the named range =Indirect(A2)

The above is simplified. My real formula in the A2 data validation is:
=INDIRECT(SUBSTITUTE(LEFT($A$1,FIND(" ",$A$1)-1),"-","_"))
(I am only using characters up to the first space as my rangename and am using substitute to convert characters that are invalid in named ranges.)

To create the named ranges I use a PivotTable:
Rowfield1 = Division = Layout = Outline, no subtotals
Rowfield2 = Region
Datafield = Count of market
This creates a list, with no duplicates similar to:
Division1
Region1 26
Region2 43

Division2
Region3 6
Region4 33

I copy the Pivot Table to values then use Autofilter on the Division column to show only non-blanks. Then in the Region Column paste a formula to get the Division into the same column as the Region. In my case this is an example:
=LEFT(G11,FIND(" ",G11)-1)
Again I am only using characters up to the first space.

My list has become:

Division1
Region1 26
Region2 43

Division2
Region3 6
Region4 33

I name the cell containing Division1 "XXX" then use this code to loop through using Insert, Name, Create to create / redefine the named ranges.

    Range("XXX").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.CreateNames Top:=True, Left:=False, _ 
         Bottom:=False, Right:= False
    Selection.End(xlDown).Select
        
    Do Until Selection.Row > 1000
        Selection.End(xlDown).Select
        If Selection.Offset(1, 0).Formula <> "" Then
            Range(Selection, Selection.End(xlDown)).Select
            Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
                False
         Selection.End(xlDown).Select
         End If
    Loop
    
End Sub

Gavin