Advanced Filtering

[James]

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 Morin]

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

 

[James]

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).