Multiple selection criteria
 

I have a worksheet with employee name, whether they are certified and their
function in separate columns. I want to use a formula to select only those
employees who are certified and perform the audit function and put the
results in a separate column. Is there a simple way to perform this
operation? I seem to remember seeing an article relative to this but can't
put my hands on it now.
Thanks

Multiple selection criteria
 

What about trying Filter Auto FIlter

"Bigkahuna" wrote:

I have a worksheet with employee name, whether they are certified and their
function in separate columns. I want to use a formula to select only those
employees who are certified and perform the audit function and put the
results in a separate column. Is there a simple way to perform this
operation? I seem to remember seeing an article relative to this but can't
put my hands on it now.
Thanks

Multiple selection criteria
 

No I want to have a column with the names of those who meet multiple
criteria. I actually need several columns with employees who meet difference
sets of criteria so auto filter won't provide what I need.

"Pimamedic" wrote:

What about trying Filter Auto FIlter

"Bigkahuna" wrote:

I have a worksheet with employee name, whether they are certified and their
function in separate columns. I want to use a formula to select only those
employees who are certified and perform the audit function and put the
results in a separate column. Is there a simple way to perform this
operation? I seem to remember seeing an article relative to this but can't
put my hands on it now.
Thanks

Multiple selection criteria
 

Try this array formula**. Note that this is slow to claculate if you have
1000's of rows of data.

Named ranges:

Name, refers to A2:A100
Status, refers to B2:B100

Enter a formula i a cell that returns the count of records that meet the
criteria.

E1: =COUNTIF(Status,"certified")

Extract the names where the status is certified.

Array entered** in E2:

=IF(ROWS(E$2:E2)<=E$1,INDEX(Name,SMALL(IF(Status=" certified",ROW(Name)),ROWS(E$2:E2))-MIN(ROW(Name))+1),"")

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Bigkahuna" wrote in message
...
No I want to have a column with the names of those who meet multiple
criteria. I actually need several columns with employees who meet
difference
sets of criteria so auto filter won't provide what I need.

"Pimamedic" wrote:

What about trying Filter Auto FIlter

"Bigkahuna" wrote:

I have a worksheet with employee name, whether they are certified and
their
function in separate columns. I want to use a formula to select only
those
employees who are certified and perform the audit function and put the
results in a separate column. Is there a simple way to perform this
operation? I seem to remember seeing an article relative to this but
can't
put my hands on it now.
Thanks