Spreadsheet improvement

I am trying to create a better and more efficient version of this matrix. The purpose of it: keep track of required training for each employee and a history of them. As seen in the first tab, some trainings are required, some optional and some not required. So maybe some conditional formatting in case that a date is entered where is not supposed to.

I would like to include information like:

First name
Last Name
Employee ID
Employee Department#
Employee Job Title
Date of Hire
Name of training

I would also like to be able to count the employees that had a certain training done, to be able to filter everything and more important, to be able to see who should have done a training but missed it. After all, the matrix should keep track of training that was done and that wasn’t so we would be in compliance.

The way I am thinking maybe a form that would collect the data and with a macro store them in a separate spreadsheet where information can be manipulated easier… The spreadsheet, however, is kept by somebody that is not to advanced with excel, so it would have to be very used friendly.

And maybe I would add termination date so I can filter active and terminated employees. So far the information for inactive employees was moved on a different tab.

Any help and ideas are much appreciated.

Thank you!

PS Would this work better in Access? I have some minimal knowledge of Access. Enough to understand what’s going on, but not to build my own database. However, I know that the final result can be made to be very user friendly. So I would be open to a change.

Competency matrix test.xlsx

Hi, I would divide the info into 3 separate tables as follows, assuming that there is more than one training session per employee.

in a different tab I would put the "query" to count active employees trained on an specific course , etc

I would use IDs intead of names to minimize input errors

MASTER
First name
Last Name
Employee ID
Employee Department#
Employee Job Title
Date of Hire
Date of Dismissal

TRAINING SESSIONS
EMPLOYEE ID
COURSE ID
DATE OF TRAINING


COURSE INFO
COURSE ID
NAME OF TRAINING

So you would choose to have it in Access?

Would I have options to see the employees that were supposed to have a traing but have nothing recorded or what employees need a certain training? I would do that based on the thype of training.

Shall I add a second table because I would have

Course ID
Employee Job Title
Course Type (required, optional, not required)

Or shall I have this in the third table?

Thank you

I think that would be a table needed if you were to set up a query for WHO needs training still...

You could do this either way EXCEL or ACCESS

The course attendance record could be as follows

HEADER

Course ID
Date

DETAIL

COurse ID
Employee ID

and then have a macro populate the MASTER SHEET with the date based on the COURSE ID and EMPLOYEE ID