Hello All! At work I have been tasked to redo the training excel sheet that someone else created. It has absolutely no formatting, and I know it should be set up differently.
So I am asking for recommendations for the best way to set it up. I would appreciate if you all can let me know if I am on the right track or not.
My Thought Process: I have to track basic employee info (name, job title, start date, Base location) I have to track each of the courses (Course Title, Duration, Location, Type, Freq Due) I was thinking of using Slicers so it is easier for staff to filter the data.
1 sheet for Employee Data (Table). 1 sheet for Course Data (Table). (For Data Entry) 1 sheet for Training Log (Table) - Staff Name, Base Location, Course Title, Date Completed, Next Due (Calculated).
Then create a pivot table / Slicers (Used by staff to view data).
Thanks for the help. I appreciate it.
Kevin Robertson 10 months ago
Please provide some screenshots of the sheets in their current state so we can see what you are working with.
Thank you both for replying.
Richard would love to use a database, but for now will have to use what i have as others are working on the workbook too.
Kevin
The original workbook was created with 5 worksheets. One for employment, initial training, refresher training, mandatory e-learning, and mandatory additional.
Basically each course has 2 columns. Due Date, Completion Date. Some have expiry date. Some are simple yes / no.
Some courses only need to be taken once, and others reoccur each year, or 3 years, etc. Each worksheet has 2 Header Rows. One for Course, the other for due and completion dates.
I will need to create pivot tables (for reports) and slicers (for easy use). Will probably have to combine header rows or pivot tables will be hard to create. Not sure if I should just use 1 long spreadsheet for all data, and then create separate worksheets for reports.
Can we see some samples of the data (black out sensitive info, of course).
Ramona WoitasOP 10 months ago
This is what I Started with and I have been trying to clean things up. I have then added each worksheet as a table.
I have copied some of the columns from each worksheet. Each worksheet starts with Employee.
Unfortunately for each worksheet, you have to retype the Employee Name, Number, Status, Home Base Campus and Title. I will have to look up how to automatically have it so that if a new name is added to the 1st worksheet, it it automatically added to the other worksheets. My worry is what if the other worksheets are filtered, can I still do it.
Ramona WoitasOP 10 months ago
Ramona WoitasOP 10 months ago
Ramona WoitasOP 10 months ago
Ramona WoitasOP 10 months ago
Ramona WoitasOP 10 months ago
Ramona WoitasOP 10 months ago
Ramona WoitasOP 10 months ago
Last 5 Pics - The 5 worksheets in the workbook that i have cleaned up a bit.
I'd forgot the original worksheet and don't try to retro fit it.
Pretend it's a brand new project and get the requirements for what is needed.
Write them all up and start from there.
Is there a reason you can't make it a db instead?
Can always pull the data into Excel for different ways to display it.
Ramona WoitasOP 10 months ago
I would love to make it into a database, but I don't have the time to write it up right now, and staff are not very good with excel or databases. Admin staff are working overtime to enter in all of the training for 160 + staff, from certificates in the staff physical files. Supposed to be done by Apr 30th. LOL
Right now I am just trying to fix things enough to use sort / filter etc. After initial info is entered, I will be the one in control of the data, thank god. Then i can take the time to either set up multiple pivot tables to use for reports. Or better yet, talk management into using a database instead.
Kevin Yip 10 months ago
You need a database, because that is the solution for your original problem of lack of formatting, lacking of structure, etc. A database gives you STRUCTURE. If your co-workers don't know how to organize the data, you have to design a database that does the organizing for them, then export the data to Excel for their viewing purposes. Excel is not the tool for organizing and structuring data; Access is. If your co-workers aren't skilled enough to use Access, you have to make forms simple enough for them to use.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Excel Forum.
The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.