Advanced Filtering

I have 13 similar lists on different sheets, that are all
filtered automatically based on the selection made in a
drop-down box on another sheet, using a simple macro that
initiates advanced filtering on each sheet.

Up until now 2 critera columns have been used for all
filtering options, and I've had no difficulties.

Now, I want to use a third critera column. For almost all
of the options, it will not matter what is in this
column. However, in one instance, I will want to see only
rows with a particular text string. In another instance,
I will want to exclude only the rows with that same text
sting. For example:

E28: Number F28: Schedule G28: Occupation Code
E29: 1111 F29: A G29: 01
E30: 1112 F30: B G30: 02
E31: 1113 F31: G G31: 01
E32: 1112 F32: A G32: 04
etc...

"Number" & "Occupation" are already taken care of with ""
and "" criteria that compare to minimum and maximum
values listed in a table, and selected using a lookup
function based on the value returned by the drop-down box
selection. The necessary formulas are always in place,
and are either TRUE or FALSE according to the selection
made by the user.

How can I have it so that the values in the "Schedule"
column will not affect the filtering in most cases, but
will show exclusively rows with "A" in one case, or
everything except rows with "A" in another case?

Thanks.

To show "A", use this formula as part of your criteria
range:

=(E29 criteria1)*(F29 = "A")*(G29 criteria2)

For everything but "A", switch = "A" with "A".

HTH
Jason
Atlanta, GA

-----Original Message-----
I have 13 similar lists on different sheets, that are all
filtered automatically based on the selection made in a
drop-down box on another sheet, using a simple macro that
initiates advanced filtering on each sheet.

Up until now 2 critera columns have been used for all
filtering options, and I've had no difficulties.

Now, I want to use a third critera column. For almost
all
of the options, it will not matter what is in this
column. However, in one instance, I will want to see
only
rows with a particular text string. In another instance,
I will want to exclude only the rows with that same text
sting. For example:

E28: Number F28: Schedule G28: Occupation Code
E29: 1111 F29: A G29: 01
E30: 1112 F30: B G30: 02
E31: 1113 F31: G G31: 01
E32: 1112 F32: A G32: 04
etc...

"Number" & "Occupation" are already taken care of
with ""
and "" criteria that compare to minimum and maximum
values listed in a table, and selected using a lookup
function based on the value returned by the drop-down box
selection. The necessary formulas are always in place,
and are either TRUE or FALSE according to the selection
made by the user.

How can I have it so that the values in the "Schedule"
column will not affect the filtering in most cases, but
will show exclusively rows with "A" in one case, or
everything except rows with "A" in another case?

Thanks.
.

Jason, I need a formula that will automatically filter
properly, depending on what the user selects in the drop-
down box. I can't have them manually changing a formula
from "=" to "", etc.

When you select from an option box, you return a value. I
have 16 options, so when a choice is made, it will return
a number from 1 to 16 in a specified cell. When 13 is
selected, I need to filter column F to show only rows
with "A". When 14 is selected, I need to filter column F
to show only rows without "A". When 1-12 or 15-16 is
selected, I need for column F to play no role in the
filtering process (filtering is always also based on
columns E and G, but I already have formulas in those
columns that work fine).

-----Original Message-----
To show "A", use this formula as part of your criteria
range:

=(E29 criteria1)*(F29 = "A")*(G29 criteria2)

For everything but "A", switch = "A" with "A".

HTH
Jason
Atlanta, GA

-----Original Message-----
I have 13 similar lists on different sheets, that are
all
filtered automatically based on the selection made in a
drop-down box on another sheet, using a simple macro
that
initiates advanced filtering on each sheet.

Up until now 2 critera columns have been used for all
filtering options, and I've had no difficulties.

Now, I want to use a third critera column. For almost
all
of the options, it will not matter what is in this
column. However, in one instance, I will want to see
only
rows with a particular text string. In another
instance,
I will want to exclude only the rows with that same text
sting. For example:

E28: Number F28: Schedule G28: Occupation Code
E29: 1111 F29: A G29: 01
E30: 1112 F30: B G30: 02
E31: 1113 F31: G G31: 01
E32: 1112 F32: A G32: 04
etc...

"Number" & "Occupation" are already taken care of
with ""
and "" criteria that compare to minimum and maximum
values listed in a table, and selected using a lookup
function based on the value returned by the drop-down
box
selection. The necessary formulas are always in place,
and are either TRUE or FALSE according to the selection
made by the user.

How can I have it so that the values in the "Schedule"
column will not affect the filtering in most cases, but
will show exclusively rows with "A" in one case, or
everything except rows with "A" in another case?

Thanks.
.

.

If you are only trying to retrieve a group of rows with the same address in
one cell, probably the AutoFilter would be quicker and easier than the
AdvancedFilter...........

Vaya con Dios,
Chuck, CABGx3



"Oscar" wrote in message
...
Well I have my data set up so that I can extract it to another sheet. I go
thru advance filtering once for my current weeks data to look for
duplicates. Then I copy those duplicates go back to the criteria and paste
it, I run advance filter again and bam I have all the duplicates with that
address. Just what I Was looking for.
1. Is there a faster way of doing this. (since I added more rowsto
criteria, and I am copying to another sheet, the list range does not
populate automatically I have to highlight it?
2. I would also like to have my current week filter in to the same page as
my duplicates. is there a command that I can put in the criteria that tells
it filter in these nine records and also this weeks data?

Criteria

TYPE | ADDRESS

20030303

104 Park Ct.

25540 S. Canal St.

1813 Burry Cr.
What can I put in front of the date thot would filter the data for that
week also into my extracted sheet?

Thanks