Multi-Select Dependent List Boxes Possible

[jaysunice]

Here's the situation. I more or less have a tiny "database" (if you'd even call it that) that contains:

1. Brands
2. Products
3. Descriptive Categories

Each Brand (15) can contain anywhere from 2 to 30 different Products. Each Product, contains the same set of 15 Descriptive Categories.

***

I would like to create a way to have a mult-select list box drop down that would allow me to choose one or more of the Brands. Then, a second dependent list would allow me to choose (one or more) amongst the relevant Products for the associated Brands that were just selected. Finally, a query button that returns the pertinent information in a table format.

***

I know how to create dependent lists using =Indirect() via Data Validation lists, however, this does not translate to Mult-Select List Boxes.

Many Thanks!

 

[ndendrinos]

welcome to the board jaysunice
see if this can help:
http://www.box.net/shared/oouv3icpyr

 

[Andrew Poulsom]

Welcome to MrExcel.

Couldn't you use a pivot table?

 

[jaysunice]

Welcome to MrExcel.

Couldn't you use a pivot table?

It might get messy because some of the descriptive categories are lengthy -- but a good consideration. Neverthless, this doesn't get around the dependent-list option. Even if you put the Brand and Product in the Page Fields, they're not context-dependent.

I actually might pull this into Access and make a little mini-application.

 

[jaysunice]

Removing because I spotted my error.

 

[dksr84]

Hi I have this same query. to be more particular I come from a pharma field wherein you have multiple trials. The comparator drugs may or maynot be the same in these trials. it is possible that i could have all my trials in one list box and the products associated in a dependent list box. the catch here is I dont want a duplication of the product names if they seem to coincide.

Could some one please help me with this/

 

[MickG]

With your data as below in sheet 1, and Combobox1 and Listbox1 in sheet2, then:-
Right click sheet2 "Tab" , select "View Code" and Paste the Code below.
NB:- Combox1 will load when you Activate sheet2 and ListBox1 will fill when you select from Combobox1
NB:- Ensure there is a blank line at the end of each "Product Range "

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("B:B").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]If[/COLOR] Dn(1).Offset(, -1).Value = ComboBox1.Value [COLOR="Navy"]Then[/COLOR]
        ListBox1.List = Dn.Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Activate()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, r [COLOR="Navy"]As[/COLOR] Variant, Sp [COLOR="Navy"]As[/COLOR] Variant, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A:A").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]End[/COLOR] With
ComboBox1.Clear
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    ComboBox1.AddItem Dn.Value
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[TABLE="width: 122"]
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="class: xl63, width: 27, bgcolor: #DAEEF3"] [/TD]
[TD="class: xl63, width: 72, bgcolor: #DAEEF3"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: #DAEEF3"]B[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]1[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]Trial 1[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"]Product 1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]2[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: white"]Product 2[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]3[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] [/TD]
[TD="class: xl65, bgcolor: #DCE6F1"]Product 3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]4[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: white"]Product 4[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]5[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] [/TD]
[TD="class: xl65, bgcolor: #DCE6F1"]Product 5[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]6[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]7[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"]Trial 2[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"]Product 6[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]8[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: white"]Product 7[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]9[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] [/TD]
[TD="class: xl65, bgcolor: #DCE6F1"]Product 8[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]10[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: white"]Product 9[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]11[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] [/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]12[/TD]
[TD="class: xl66, bgcolor: white"]Trial 3[/TD]
[TD="class: xl66, bgcolor: white"]Product 10[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]13[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] [/TD]
[TD="class: xl65, bgcolor: #DCE6F1"]Product 11[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]14[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: white"]Product 12[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]15[/TD]
[TD="class: xl65, bgcolor: #DCE6F1"] [/TD]
[TD="class: xl65, bgcolor: #DCE6F1"]Product 13[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]16[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: white"]Product 14[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

If you have a flat file with Brands, Products and Descriptive Categories as headers, this sounds like what AutoFilter would do.

 

[dksr84]

Thank you so much for the suggestion. I have another report built based on the auto filter option. But in this particular case the headers of the report are going to come in from the database.

Is there a possibility to attach the file here to demonstrate what I am trying to do.

 

[dksr84]

Thank you so much for the suggestion. I have another report built based on the auto filter option. But in this particular case the headers of the report are going to come in from the database.

Is there a possibility to attach the file here to demonstrate what I am trying to do.

I have uploaded the file here: https://app.box.com/s/zqxpy312n9ciy22hvja4z1oamue4hbin

The report page has the userform which would help me create the report. The Mastersheet is the database.