advanced filter

[SteveDB1]

Morning all.
As I've read through the posts on filtering, I see a lot of discussion on
using the Advanced Filter to create a custom filter mechanism.
When I found my advanced filter in 2007, it only had one criteria option.
I then tried using it in "both directions" for my desired filter and it
didn't do what I needed.

Is there a way to set a filter to link the filter to two worksheets? I.e.,
if I pick an element on one page, both that page, and the other page become
filtered?

Thank you.
SteveB.

 

[ShaneDevenshire]

Hi,

You need to clarify:

1. What do you mean that Advanced Filter only had one criteria option? The
dialog box is exactly the same as in 2003 and all earlier versions. If you
mean there can be only one criteria range with 10,000,000 conditions, that is
correct.
2. What do you mean "filter in both directions". Are you trying to sort or
filter. Or do you mean you are tryiig to filter all items that meet
condition A and then all items that don't meet condition A. The criteria
range would look like this
First Name or First Name
Smith <>Smith

You might need to be careful with these criteria but the basic concept works.

3. If you want to pick a filter on one page and have it filter two pages you
will need to consider VBA. Do you want a programming solution?

 

[SteveDB1]

Morning Shane,
Thank you for your response.
My answers are mixed in with your questions.

Hi,

You need to clarify:

1. What do you mean that Advanced Filter only had one criteria option? The
dialog box is exactly the same as in 2003 and all earlier versions. If you
mean there can be only one criteria range with 10,000,000 conditions, that is
correct.

In the advanced filter window, it asks for a filter range, a criteria range,
to filter in place, or copy to another location.

2. What do you mean "filter in both directions". Are you trying to sort or
filter. Or do you mean you are tryiig to filter all items that meet
condition A and then all items that don't meet condition A. The criteria
range would look like this
First Name or First Name
Smith <>Smith

You might need to be careful with these criteria but the basic concept works.

The basic autofilter allows me multiple selctions, if I choose to filter
multiple columns-- which is my standard practice. I.e., I normally select 6
to 8 columns, and then have 6 to 8 filters for that one worksheet.

3. If you want to pick a filter on one page and have it filter two pages you
will need to consider VBA. Do you want a programming solution?

Yes, I would like a programming solution.

How would I perform the task of having this?

Thank you.

 

[SteveDB1]

Shane,
My goal is to set a filter on both worksheets-- thus far I've been using
autofilter-- for 6 to 8 columns, then on my primary worksheet select the
elements I want filtered, and it automatically sets the filter to those same
elements on my secondary worksheet.



Thank you.

 

[SteveDB1]

3. If you want to pick a filter on one page and have it filter two pages you

will need to consider VBA. Do you want a programming solution?

Shane,
I've decided to try to record a macro that does the basics of what I'm
looking for, and this is the code that it created.

Sub FilterB()
'
' FilterB Macro
'

'
Selection.AutoFilter
Range("A8:F8").Select 'my range varies from file to file.
Selection.AutoFilter
Sheets("Pg 5_09-29-2008").Select 'the sheet name is always Pg5, but the
date 'will always vary.
Selection.AutoFilter
ActiveSheet.Range("$A$3:$J$29").AutoFilter Field:=6, Criteria1:="<>"
' my range will always vary. Field 6 is constant, I'm not clear why it only
'shows "<>" for my criteria.
Sheets("Sum").Select 'this worksheet has some variation on Sum, SUM,
'Summary, etc....
ActiveSheet.Range("$A$8:$F$13").AutoFilter Field:=1, Criteria1:="<>"

End Sub


In looking at the elements, I want to connect the filter operation between
two worksheets.

I.e., Pg5_Date is my primary sheet. Sum is my secondary sheet.
My goal is to perform the same filter on the Sum sheet as I do on Pg5_Date,
automatically.

This is to reduce the time that I spend doing the filter on each sheet--
going back and forth for specific criteria in fields 1 and 6.
So that if I set my field 6 criteria on page 5, it will automatically set my
field 1 criteria on Sum.
There are some files that have well over 500 rows of data for Pg5 sheets.

Going back and forth between the two worksheets is taking way to long, and I
want to shorten the process-- by what seems a feasible process of "linking"
the two filters into a single operation.

So, my question is:

How can I make the filter on Pg5 activate and match the filter operation on
Sum?

Thank you.
SteveB.