Hi

This is what you need to do. You have to use Data/Filter/Advanced Filter
option.

I am assuming your data is in A1 to say F1000. I am also assuming that row 1
has the headers, possibly
Name, State1, State2, State3, State4, State5

Make sure that your header is in a bolder and/or bigger font than the rest
of the data(This helps
Excel to figur out the header row.)

In H1 to L6 we will need to create a Criteria Range. Copy B1 to F1 and Paste
it in H1 to L1.
In effect you are creating the names of the fields on which you want to
apply the criteria.
If you want to search for CA, you will type CA in cells H2,I3,J4,K5 and L6.
Just like in Access
each separate row signifies an OR condition. (the criteria entered in the
same row is an AND
condition). So what we are saying here is that (State1 is CA) OR (State2 is
CA) OR..

Now click on the menu option Data/Filter/Advanced filter.

Select Copy to another location.
Click in the List range box and Type or otherwise indicate the source range
$A$1:$F$1000
Click in the Criteria range box and type or otherwise indicate the range
$H$1:$L$6
Click in Copy to: box and type the cell where you want to copy the extracted
data.
Click on OK.

Alok




"LucaBrasi" wrote:

I have a problem that I need to solve in a way other than the way I
always do it. OK, here goes:

I have a worksheet with 6 columns: The first column contains the name
of an employee. The next 5 columns contain 2 char US state
appeviations. These 5 columns represent the area of responsibility for
the employee in column 1. The distribution is more or less random, but
each of the employees has exactly 5 states. Now, what I would like to
do is type the 2 char code in an input box or cell, and have the
worksheet filter out any employee who does not have that state code in
any of the 5 columns, and output that report to a printer. In the
unfiltered worksheet, there are about 1800 records.
Now I have been doing this for a long time by importing the file to
Access, filter the records I want and then outputting to Excel. The
obvious reason for this is that I am proficient in Access, but not
Excel. Any info that could jump start me towards an Excel-only
solution would be much appreciated !