tracking employee required training, who HAS NOT completed

[jynxm]

Good morning,

I am using excel 2010 and I have 2 sheets that I work with regularly. One is a list of staff, both current and past. This sheet is set up to filter out 'current' and other employees based on their employment status. The other is a sheet that I use to track all of the training they've completed .. again for both current and past employees. The sheet currently has about 1800 entries dating back to about 2000.

So now, I need to pull a report of who (of the current staff only) DID NOT complete one of the required courses in this fiscal year. Then I can let them know the course is being offered again.

I can print a nice little pivot table of who has completed the training, and on what dates the training was offered - but I need it to tell me also who has not.

any ideas?

 

[Roderick_E]

isn't it the same process? Simply set your pivot table to show employees who have not completed X course(s)

 

[jynxm]

hmmm.. sounds easy enough, how do I do that though?

 

[Roderick_E]

In the pivot table, what filter is determining the training has been done? Then do the opposite.

 

[jynxm]

OK, I have 2 report filters, 'employee status' and then 'department' (because not every employee in my list is current and not every department requires the same training)
Row labels are 'office' (because there are 2) and then 'Employee name'
Column label is the 'required training', filtered to just the one I am looking for
Values is a 'count of required training'. (I've also tried a 'count of the employees' which gives me the same result)

This gives me a list of who has completed the training I am inquiring about, but does not list the employees who have NOT completed it. they (the ones who have not completed) are removed from my list on the right hand side all together. I'd like for my full employee list to stay intact OR only list the ones who have NOT completed.

Maybe I've been tracking the info wrong .. ? I only record when a course has been taken .. although I do have a list of what is required ..
I am going stir crazy and this is going to be something simple I know it lol I will be kicking my own butt after LOL

Another, similar situation would be: if you had a list of required items that each person needed to have, how would you track what is outstanding? Maybe I am over complicating things ..

 

[Roderick_E]

Maybe on the data, indicate if training is complete as a 1 and not as a zero then you could set up filters that way?