Nested "filters" of a list

[Guest]

Hi,

I've searched the community pages for a bit to see if I could find a
solution to my problem there; however, my lack of VBA and UserForms knowledge
meant I couldn't find a solution I could understand.


I have a worksheet called "Database" where I have a huge list of items that
contain numerous subsets within subsets.

************
Example:
- Item "A"
- Subset "A1"
- Subset "A1a"
- Subset "A1ai" --> Data A1ai
- Subset "A1aii" --> Data A1aii
- Subset "A1b"
- Subset "A1bi" --> Data A1bi
- Subset "A1bii" --> Data A1bii
- Subset "A2"
- Subset "A2a"
- Subset "A2ai" --> Data A2ai
- Subset "A2aii" --> Data A2aii
- Subset "A2b"
- Subset "A2bi" --> Data A2bi
- Subset "A2bii" --> Data A2bii
- Item "B"
- Subset "B1"
- Subset "B1a"
- Subset "B1ai" --> Data B1ai
- Subset "B1aii" --> Data B1aii
- Subset "B1b"
- Subset "B1bi" --> Data B1bi
- Subset "B1bii" --> Data B1bii
- Subset "B2"
- Subset "B2a"
- Subset "B2ai" --> Data B2ai
- Subset "B2aii" --> Data B2aii
- Subset "B2b"
- Subset "B2bi" --> Data B2bi
- Subset "B2bii" --> Data B2bii
....etc.
************

I'd like to be able to:
1. In cell "A1" of a worksheet called "Form": select from a list of items
A-M from worksheet "Database"
2. In cell "B1" of worksheet "Form": immediately select from a list of
subset items associated with the item selected in cell "A1" without having to
run a secondary marco (unless it's one that automatically runs once the 1st
item is selected)
3. In cell "C1" of worksheet "Form": immediately select from a list of
subset items associated with the item selected in cell "B1" without having to
run a secondary marco (unless it's one that automatically runs once the 2nd
item is selected)
4. etc. for all subsets
5. have the resulting data fed into specified cells in the "Form" worksheet


Also, is it possible to do this without writing a macro, as my knowledge of
VBA is fairly minimal and of UserForms is 0. If not, as I assume, that's OK,
I need to learn VBA and UserForms anyway.

Many MANY thanks in advance!

 

[loudfish]

Also, is it possible to do this without writing a macro, as my knowledge of
VBA is fairly minimal and of UserForms is 0. If not, as I assume, that's OK,
I need to learn VBA and UserForms anyway.

Many MANY thanks in advance!

I'm not really sure what you're meaning, but it sounds like multiple
filters might avoid having to use VBA/Forms (but this depends on how
your data is organised). If your data looks like this, or can be
organised like this:

ColA ColB ColC ...
Spain Bob Apples
France Tom Pears
UK Mike Apples
Spain Mike Apples
UK Tom Pears
UK Bob Apples

(ie the filter criteria are all in separate columns).

highlight the whole table. Data....Filter...AutoFilter...
Then choose the criteria that you want
e.g. UK from ColA
Tom from ColB
etc..
The rows returned will be the rows which match all criteria.

HTH

Andrew

 

[Guest]

Hi Andrew,

Thank you for replying so quickly, I really appreciate it.

Yeah, I'm also not the best at conveying what I mean. So let me try again:

In the worksheet "Database", I have 3 criteria (Country, St/Prov, and City)
and 3 items of data (Area, Pop, and Elev) that correspond to the individual
criteria:

Country St/Prov City Area Pop Elev
USA NY NYC 469 8.1e6 33
USA NY Alb 22 .1e6 200
USA NY Buff 53 .3e6 26
USA Ca LA 498 3.8e6 0
USA Ca SF 47 .7e6 52
USA Ca SD 372 1.2e6 72
Canada BC Whistler 62 .009e6 2200
Canada BC Van. 44 .6e6 548
Canada Alberta Calg 281 1.0e6 3438
Canada Alberta Edm 264 .7e6 2192


For example's sake, let's say I wish to populate the worksheet "Form" with
the Area, Pop, and Elev of SD. And I'd like to select my 3 criteria from the
worksheet "Form", even though the data I want resides in the worksheet
"Database".

I want to select the Country in cell A1, the St/Prov in cell B1 and the City
in cell C1:

1) When I select the pull-down menu in cell A1, I'd like "USA" and "Canada"
to appear for selection --> I then select "USA"

2) after I select "USA", when I select the pull-down menu in cell B1, I'd
like "Ca" and "NY" to appear without having to run a secondary macro (unless
the macro automatically starts upon selection of "USA") --> I then select "Ca"

3) after I select "Ca", when I select the pull-down menu in cell C1, I'd
like "LA", "SF" and "SD" to appear without having to run a secondary macro
(unless the macro automatically starts upon selection of "Ca") --> I then
select "SD"

4) after I select "SD", I'd like the Area, Pop, and Elev of SD, from the
"Database" worksheet, to be input into the cells I specify in the "Form"
worksheet.


So, it's like a "filter"; however, I'd like to be able to switch over to the
"Database" worksheet at any time and see ALL the info within that worksheet
instead of having all the rows that do NOT meet my criteria be hidden, as
with what happens with a regular filter operation.


My apologies for the novel I just wrote, I just wanted to make sure I was
clear, again, as I'm not the best in explaining myself.

Thanks again!

 

[loudfish]

My apologies for the novel I just wrote, I just wanted to make sure I was

clear, again, as I'm not the best in explaining myself.

Sorry it's taken a while to get back to you. Your description is very
clear this time - I can see your filtering requirement.

Do you really need the selections to be on a separate worksheet? With
Excel's native autofiltering, which I described above, the drop-down
options which get displayed to the user will be restricted by other
filters, just as you need (ie only displaying LA, SF, PD when CA is
selected on the City).

If you do need them on separate worksheets, then can I suggest the
following alternative solution.

1. User enters ColumnA filter in Cell A2.
2. User enters ColumnB filter in Cell B2.
3. User enters ColumnC filter in Cell C2.

Note that drop-downs in B2 & C2 do not automatically restrict based on
selection based on A2 - this is the bit of the requirements that are
not met. (I'm pretty sure it is possible to do this bit, but AFAIK,
only with quite a bit of VBA coding around combo boxes).

Once filled in all the filter criteria, you hit a "apply filters"
button, which filters the database worksheet according to the
criteria.

This can be done with advanced filters, and some simple VBA code
should give you what you need.

1. Get the Advanced Filters working how you need them. See
Contexture's description of advanced filters - http://www.contextures.com/xladvfilter01.html
(by Debra Dalgleish). It is possible to set this up so that you have
the table on one page, and the filter criteria on a different
worksheet.

2. Buttons and Automation. For your users, you can create two buttons,
linked to VBA code.

one "apply filters", including code like this:
Range("A716").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("Sheet2").Range("J16:L17"), Unique:=False
(make the range names tie to what you need, I would recommend using
"Named Ranges")

one "remove filters", including code like this.
ActiveSheet.ShowAllData

There is a bit more to the VBA than just these two lines, but really
just activating the right worksheets - you should be able to use the
recorder for all of this.

For the missing criteria (drop-downs which dynamically update), my gut
feeling is that you'd need combo boxes (which have defined lists
behind them), linked to a chain of on update events that update the
"defined lists" whenever any of the selections change - ie you select
USA, it triggers a filtering of the "database" and a rebuild of the
"defined lists" for the other combo boxes based on the filtered lists.
Tricky. If you want to go down this route, I'd start with a solution
that misses this bit out, then look at adding it once the other stuff
works.

HTH

Andrew

 

[Guest]

Hi Andrew,

Yeah, I was afraid it was going to be fairly difficult to make it work the
way I was hoping.

I'll see what other options are available.

Thanks again for the help,

EdE

 

[Guest]

Hi again Andrew (or whoever happens across this),

OK, so I've learned a bit about ComboBoxes and have managed to set up what
you had discussed earlier: where the defined list of ComboBox2 is based on
the selection of ComboBox1.

So, my delimma now consists of this:
I have another list to perform the same action; however, the database has
over 5000 combinations of selections.
My ? is, is there any way to reference a filter's defined list (the list
given when you click the drop-down arrow of a filter) in a macro so that the
List array of the ComboBox is that of the filter's defined list.


Thanks again,

EdE

 

[loudfish]

OK, so I've learned a bit about ComboBoxes and have managed to set up what
you had discussed earlier: where the defined list of ComboBox2 is based on
the selection of ComboBox1.

So, my delimma now consists of this:
I have another list to perform the same action; however, the database has
over 5000 combinations of selections.
My ? is, is there any way to reference afilter'sdefined list (the list
given when you click the drop-down arrow of afilter) in a macro so that the
List array of the ComboBox is that of thefilter'sdefined list.

Hi again. You're starting to extending beyond my knowledge of the
Object library in Excel, but have a play around with this code - it
will create a control box (not a combo box), set the listfillrange,
then retrieve this from the object into a msgbox.

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
lb.ControlFormat.ListFillRange = "A1:A10"
MsgBox (lb.ControlFormat.ListFillRange)
End With

If this doesn't help, I would repost your question under a fresh
thread.

HTH

Andrew