Forum Discussion
Excel
Some questions for you.
- Are you open to changing the way you do this?
- How experienced are you with Excel (my guess is "not too much")
- What is it that is "expiring"? Certification? Licensing?
You probably--not definitely, but probably--would be better served by a single, all-encompassing database, with the records of each staff member on that single database (single spreadsheet), rather than separate ones for each. Then there'd be a single summary sheet that could show what you want in the way of summary data, by letting Excel abstract it from the single, all encompassing database. That happens to be something at which Excel excels.
Is it possible for you to post an example of what your current workbook looks like, just without any real names or other identifiable data. In place of the real names, substitute characters from your favorite films or TV shows. Doing that--posting what you're working with--would help me or others make specific suggestions.
i thought about that having all the names on the one work sheet but my boss wants the staff to be on separate worksheets as she doesn't want to see all there information she just want to look at the summary sheet and see who has expired with there name
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.
- one shows summary statistical counts of