If and/or Look Up function

[Maureen]

I have created a spreadsheet to track staff's ongoing professional
development for the year. There are 6 specific topics that HAVE to be
repeated every year, then the employee can attend any other work related
workshop they want. I want to create a formula/function that will tell me if
all 6 topics have been completed. I think I need an "IF" or "LOOKUP"
statement that looks at a specific list (containing the required workshops).
Can anyone help me construct that formula?

Thanks in advance. . . Maureen

 

[Pete_UK]

It would help if you gave some details about how your data is laid
out, what sheets, columns, rows etc that you use, perhaps with some
examples of what you have and what you want to achieve.

Pete

 

[Maureen]

Hi Pete -- Each employee is required to complete a minimum of 24 hours of
continuing education every year. In addition to the annual 24 hours they
must also complete training on specific topics, i.e.; CPR, First Aid, OSHA,
Fire Safety, etc.). It is possible for someone to have completed the 24 hour
requirment and not have any of the afermentioned training. I would like to
include a column that notes (yes or no) that these trainings have been
completed.

I have a workbook with mulitple pages; one Summary Sheet containing the
following info: Names, Contact Hours, Hours Still Needed, Requirement
Completed. And, a separate sheet for each employee which will contain
information on every workshop they have completed. Info captured on this
sheet is; Training Title, Completion Date, Presenter, and Clock Hours.

Is that enough additional info?

Thanks for your help. . . Maureen

 

[Pete_UK]

Not really, but you could count the number of mandatory sessions
using:

=COUNTIF(A:A,"CPR")+COUNTIF(A:A,"First Aid")+COUNTIF(A:A,"OSHA")etc,

and you can sum the hours spent on those mandatory sessions like this:

=SUMIF(A:A,"CPR",D)+SUMIF(A:A,"First Aid",D)+SUMIF(A:A,"OSHA",D)

Put these in the same cells in each employee's sheet (eg E1 and F1),
then you can read them more easily in your summary sheet.

Hope this helps.

Pete