M
Max
I am trying to perform several tasks in Excel 2002 for which I would
appreciate assistance.
In a listing I have the training information for staff set out as follows:
Paycode Name Course Name Next Due Compliant
059 Person A Induction 10/10/09 Yes
059 Person B Induction 9/7/09 No
059 Person A Customer Relations 2/8/09 No
059 Person B Customer Relations 2/2/10 Yes
075 Person D Induction 5/2/09 No
075 Person X Induction 11/10/09 Yes
The Compliant column contains an IF function and compares the Next Due date
against the current date to determine if that person is compliant for their
training for that course (dd/mm/yy).
What I need to achieve, either in the list (and then create the pivot table)
or in the pivot table, is:
Within each paycode –
a) Count the number of staff listed for Induction (this will give total
staff in paycode)
b) Within each course, count the number of staff who are compliant
c) Within each course, divide the number compliant (step b) by the total
number of staff counted at step a) to give percentage compliant.
The pivot table will be accessed by managers. The desired result is that
each manager will select by their paycode(s) to see a listing of their staff
against each course (there are about nine different courses) and also have
displayed the percentage compliant for each course (for each paycode). I
have around 300 paycodes with varying numbers of staff within each.
I may be asking for the impossible (or possible but the amount of
manipulation required makes it impractical) but look forward to hearing from
anyone who may be able to help.
appreciate assistance.
In a listing I have the training information for staff set out as follows:
Paycode Name Course Name Next Due Compliant
059 Person A Induction 10/10/09 Yes
059 Person B Induction 9/7/09 No
059 Person A Customer Relations 2/8/09 No
059 Person B Customer Relations 2/2/10 Yes
075 Person D Induction 5/2/09 No
075 Person X Induction 11/10/09 Yes
The Compliant column contains an IF function and compares the Next Due date
against the current date to determine if that person is compliant for their
training for that course (dd/mm/yy).
What I need to achieve, either in the list (and then create the pivot table)
or in the pivot table, is:
Within each paycode –
a) Count the number of staff listed for Induction (this will give total
staff in paycode)
b) Within each course, count the number of staff who are compliant
c) Within each course, divide the number compliant (step b) by the total
number of staff counted at step a) to give percentage compliant.
The pivot table will be accessed by managers. The desired result is that
each manager will select by their paycode(s) to see a listing of their staff
against each course (there are about nine different courses) and also have
displayed the percentage compliant for each course (for each paycode). I
have around 300 paycodes with varying numbers of staff within each.
I may be asking for the impossible (or possible but the amount of
manipulation required makes it impractical) but look forward to hearing from
anyone who may be able to help.