How to make an Excel Training Tracker

Question

I currently have an excel spreadsheet that is 'manually' tracking what requirements are needed for each Job Role on one tab, and on the other, I have a column of all of our employees and what their job role is.

What format is best to connect each employee (and their role) to the requirements needed for that role and ontop of that, how can I make a format to show me which requirements are needed or not yet completed?

Answer 1

Hi Brittany Smith7,

Thanks for visiting Microsoft Community.

It seems to be a question involving data retrieval and conditional formatting. I am willing to offer you advice, but it's hard to determine a solution based solely on a textual description.

You can share a specific file and describe your needs in detail with reference to the file and data.

Looking forward to your reply.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

Hi Jonathan, I can't see where I can attach an example of the spreadsheet but i've attahed photos.

We want the Employee & Position tab to show which employees are missing a training course that is required, which would be linked from the Needs/Requirment tab

Answer 3

Hi Brittany

Perhaps the sheet can be set up like this: add row labels for all courses after the first row of the Employee&Position sheet.

Then, enter the formula in C2, and drag it down and to the right to apply to all areas.

=INDEX('Needs&Requirement'!$B$2:$E$9, MATCH(C$1, 'Needs&Requirement'!$A$2:$A$9, 0), MATCH($B2, 'Needs&Requirement'!$B$1:$E$1, 0))

A conditional format can be added to the entire area to clearly display the results, as shown in the figure:

The effect is as follows:

Would this work?

Best Regards,

Jonathan Z - MSFT

Answer 4

Hi Jonathan,

I'll give it a go, thank you :)