Sep 01 2020 07:12 PM
Sep 01 2020 07:12 PM
Hi I am working on a Staff Training tracking spread sheet. It has each staff member in separate worksheets and a main worksheet which is called a summary. Each work worksheet (staff) has all there training inside together with dates and how many days to expire. These have calculations to tell me how many days left to expiring soon or expired. what I want is in the summary worksheet that under that particular training i want it to come up with the staff members name and whether its expiring soon or expired, depending on what comes up in the cell of the staff member I hope that this makes sense.
Please I need some help
Sep 01 2020 07:26 PM
Some questions for you.
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.
Sep 02 2020 05:08 AM - edited Sep 02 2020 05:11 AM
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:
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.