Forum Discussion
Interesting Excel Request Training Roster
Good morning Excel Experts,
I'm a Training Manager and I have an Excel Workbook with multiple worksheets. This Workbook is used to track training dates and recurrent dates and reports back to a single page. I've been slowly updating this workbook over a few years but this particular answer I just can't seem to figure out to make updating it an easy process.
The issue that I have is that when we have new employees that come on board i have to add new Rows into 25 new work sheets. So adding in one name means I'm entering in data 25 times.
Each worksheet has different formulas or information in it but some information is the same across everything.
What I am hoping to maybe discover is a way to when adding a Row into the Main worksheet it then creates the new rows in each worksheet after along with inserting the information and formulas. I'm hoping that there is a way that this can be done to optimize updating.
Second Challenge is that when an employee leaves I need to move that data to a different section in the worksheets as I have to retain that information for a set amount of time before removing it on the off chance the employee returns to employment.
I realize that there isn't much information in what I am seeking. If there are any questions to better understand and allow me to get the information across please do ask and I will provide any info at all or even maybe a chat can be done so I can show what the Workbook is more clearly.
I am at the Mercy of you Excel Experts
2 Replies
- As you say it is very hard to give definitive answers without more to go on so if you can post an anonymized version of the workbook that could help a lot. That said, I've done a lot with training matrixes and know the feeling. Here are a couple idea that might help:
a) for the 1st issue you could pre-populate all the sheets with generic IDs like EMP#001, EMP#002, .... and then use a lookup to show the name from the 'main' table or blank if not found.
b) for the 2nd issue I would instead recommend adding a column to the 'main' table for active/inactive (or similar) and a matching column on each other table (then use lookup to populate) and then use conditional formatting to 'block' or 'gray' out lines with 'inactive' and/or you can use the table filters on each table to filter those lines out.mtarler I am hoping that you will be able to view this. Please let me know if you are not able to. It wouldn't let me upload a copy as it didn't support the file format?
https://johnmenzies.sharepoint.com/:f:/r/sites/MCOTrainingDepartment-ITPFueling/Shared%20Documents/ITP%20Fueling/Shared?csf=1&web=1&e=guoLDK
