Complex Employee Training Tracker

[ASA_QA]

Before reading, I want to mention that I'm mostly looking for a function or feature that can evaluate and compare data from multiple tables, and how to organize my data around it.

I've been using Excel to track the training of my company's employees for some time now, but we've been ramping up hiring and buying new equipment that requires specialized trainings, so I'm looking to inject more automation into this process. 

My old spreadsheet is filled with so many VLOOKUP's and conditional formats that I'm starting over from scratch and migrating important data. That said, I have a few really simple objectives that I have not been able to accomplish in the couple of days I've been working on it.

At it's core, this workbook will include:

• A list of employees, with assigned responsibilities (multiple) and their personal information.
• A list of approved trainings, each of which is linked with specific responsibilities (emphasis on the plural).
• A training log where I manually assign required trainings and record dates when complete.
• Some form of analysis to see a list of approved employees for each responsibility and/or a list of each responsibility that an individual employee is approved for.

The frequent addition of new trainings is key here. I want to make sure that, when a new training is developed either for all employees or for a specific piece of equipment, employees are removed from the approved list until they complete the training.

I have been trying to brute-force things with pivot tables, but for the life of me I cannot seem to develop a system that can easily produce the data I'm looking for. Here's a brief outline of my vision:

• Select a specific responsibility > Produce a list of required trainings
  
  • I'd like for it to show all prerequisite trainings for someone with that responsibility. For example, if the role is a robotic welder technician (Warehouse > Fabrication > Robotic Welder > Robotic Welder Technician) I would want it to produce the trainings required for Warehouse, Fabrication, Robotic Welders and Robotic Welder Technicians.
• Have an automated check of the training log to produce a list of the employees that have completed all of the required trainings for the selected responsibility.

Lastly (and this is really just a pipe dream compared to the rest of what I'm trying to accomplish) it would save me a lot of time if training requirements were automatically assigned to employees based on the roles they are assigned to. That way, when an employee is assigned to a new responsibility, their training assignments will automatically reflect additional training requirements. I expect to be doing this part manually for now, though.

[#2]

@ASA_QA 

Reading your description makes me think that you may be pushing the boundaries of Excel here, that perhaps something like Access (or some other relational database software) might be more suited. That said, Excel IS very powerful, and with Power Query and other tools available, might well be able to handle the tasks you've laid out.

All of that having been said, is it at all possible for you to grant us access to the actual spreadsheet (with real names and any other identifiable or confidential information removed)? A mockup representing the actual could be an alternative. Post it on OneDrive or GoogleDrive or some comparable cloud service and grant access here.

Let us know too what version of Excel you're working with. I hope, for your sake, that it's the most current version. There are some very powerful functions that are relatively new. I've already referred to Power Query. Other newer features include FILTER, which could be of great help in what you're trying to do. Here's a video that explains it well. Armed with this tool you might be able to resolve many of your needs. https://www.youtube.com/watch?v=9I9DtFOVPIg

For what it's worth, I'm retired now, but when employed had experience with (a) responsibilities for management training and (b) for a few years, was director of a project to design a corporate HR and payroll database. So I have experience in working with the raw materials you're dealing with......

[#3]

@mathetes Thank you.

I had never paid any attention to Access, but you are right in suggesting that it may be a better fit for the information I'm trying to extract. I'm completely new to the program, so it'll take some time to figure out how to organize my data appropriately, but my hopes are high!

[#4]

@ASA_QA may I have a copy of your spreadsheet?  I'm looking for something exactly like this to track trainings.