@PJS666
Well, since you came here for help, I'm going to push back a bit. One of the common mistakes made by beginners or novices with Excel is taking what may have been a paper and pencil/pen process--which, in situations like this, almost invariably had separate sheets for each individual--and transferring that process to Excel, for the profound reason that "this is how we've always done it" and "now we have a computer to make it even faster/prettier/....."
Go back and read what I wrote the first time: there was reference to using Excel to create a summary sheet; in fact you could have various summary sheets. They're often called, in this new world, "Dashboards". In an application such as yours, I could envision a minimum of two Dashboards such that:
- one shows summary statistical counts of
- how many certifications for which categories are in effect
- how many will be expiring within XX months
- which needed certifications have fewer than X staff members qualified
- etc
- another one shows for any given individual staff member (a single Dashboard sheet that can display individual data, the individual selected via a drop-down list of all names)
- what licenses/certifications are present
- which ones are about to expire
- etc
And all of this from a single database.A single database that contains all of every individual's training records.
Maybe what you need to assure your boss of is that she will never have to look at the database...it's behind the scenes. The fundamental point is that moving to the computer gives you (your organization) the opportunity not just to computerize doing things the same old way you always have, just neater and with fancy colors, but rather to let the computer do the heavy lifting of taking a store of data and parsing it out for all kinds of reports. It's an opportunity to re-think how you approach the task of record keeping and the analysis of said records.
During my career (I retired in 2002) I was at various times a manager of training and development and the Director of the HR and Payroll database for a major pharmaceutical company. Especially in the latter category I oversaw the creation of many such reports as you're talking about here, and all from a single consolidated (relational, so much more complex) database. Excel has amazing abilities, when the application is designed properly, to deliver the results your boss wants, albeit perhaps in a way different than she was envisioning.
By the way, those names in the images you posted look like they are real people. It would be prudent to go back and take those down.
If you want to continue, post a copy of the spreadsheet itself (not just images), or a few representative sheets, but--very seriously--replace the real names with fictitious ones.
