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

"loudfish" wrote:

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("A7:D16").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