Filtering a filter list

[rwrussell]

I'm using Excel 2000. I have used advanced filtering to extract a se
of data from one worksheet to another worksheet. I now want t
eliminate some of this filtered data to further refine my search. If
use the auto filter feature ("does not contain") on this new workshee
it does what I need but only allows for 2 criteria and I have 5 or mor
that I need to filter out.

Is there a way to expand the auto filter to allow more than 2 filters?
If not, I assume I can create a criteria table, but what is the forma
for "does not contain"?

Thanks

 

[ryanb.]

One way if you do not want to use VBA, try adding 5 helper columns on the
side of your data that return true/false for each of the 5 criteria. Then
use auto filters on the helper columns to hide either the true or the false
values depending on what you are looking for.

HTH,

ryanb.

 

[Debra Dalgleish]

You can list the five criteria on the worksheet, and add a column to the
table, to check the row against the criteria list.

For example, list the five criteria in cells K1:K5

Insert a column in the table, with a heading in the first row, e.g. "Found"
In the first data row, enter a formula that refers to the criteria
cells, and the cell being checked, e.g.:


=OR(ISNUMBER(SEARCH($K$1,C2)),ISNUMBER(SEARCH($K$2,C2)),ISNUMBER(SEARCH($K$3,C2)),ISNUMBER(SEARCH($K$4,C2)),ISNUMBER(SEARCH($K$5,C2)))

Copy the formula down to the last row of data
Select a cell in the table, and choose Data>Filter>AutoFilter
From the dropdown list in the "Found" heading cell, choose FALSE

 

[rwrussell]

Thank you for the suggestions, I'll give them a try.

Just to be sure I'm not missing the obvious here (which I often am!) --
I want to be sure there is not a similar and equally easy way to do
this as there is in Quattro Pro. With QP you can run a query and for
all matching records in the criteria field you have the option to
delete them. It's all built in to their query function.

Is there a similar "filter" option in Excel?

Thanks again for your help.