Masai

I've got an answer that entails the creation of a User
Defined Function. The function is

Function IsVisible(x) As Boolean
IsVisible = True
If Rows(x.Row).Hidden = True Then IsVisible = False
End Function

With this function available, enter the formula =isvisible
(B2) in cell C2 of sheet OHR Job Quals. Copy down as
required. Use the Data, Filter, autofilter on columns A
and B.

In sheet Emp Quals, enter the heading Match in C1, then
enter the formula in C2 and copy down. Again generate an
autofilter on columns A:C inclusive.

Using the OHR sheet, select the Cashier - Cafeteria from
the Job name in column A. 2 entries appear. Then go to
sheet Emp Quals, and select True from the Match column. 4
entries appear. Similarly for the other scenario you
mentioned, but you will have to reselect the match for
each change in the Job name.

HTH

Tony

-----Original Message-----
Hello All

I have two sets of data:

- list of Jobs with one or many Competency requirements.
- list of Employees with their Competencies.

I would like to create a filter scheme that allows a user
to enter a
Job, and have Excel automatically match Employees with
competencies
that are required by the Job.

For example,

Job 'Cashier - Cafeteria' requires two competencies from
each employee
working on this Job. So, in this case, there are 4
employees with these
competencies: Employee 4, Employee 7, Employee 8,
Employee 9.

Similarly,

Job 'Cleaner' requires 9 competencies from each employee
working on
this Job. In this case, there is only one employee with
all 9
competencies: Employee 8.

The attached spreadsheet shows the data sets.

How would I achieve this? Thanks.

Masai

Attachment filename:
sample.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=456541
---
Message posted from http://www.ExcelForum.com/

.