How to go about copying values/updating lists

[mr0ldie]

Ok, so I have come up with something to help the business I work for keep track of some people's performance more easily. However, some people at the office (and the other offices where this will eventually be shared) don't fully understand how it works, so I would like to make it as simple and fool-proof as possible. I'm not very experienced with Excel, but I do teach myself Java, PHP, and some other things as hobbies. I'm confident I can learn Excel as well, but I certainly need some direction.

I have attached an example of how I want it to look; ignore any reference errors or things that seem illogical--the values and some formulas are simple placeholders.

So here's how I want it to work. I want one sheet where the employees are maintained and when a new employee is added or removed, their respective records are added or removed from the 'Print' and 'Weekly Record' sheets. When information is added, it should be added on the 'Submit Info' sheet with the user pressing Submit and then the 'Print' and 'Weekly Record' sheets being populated with the averages, etc. I want the 'Weekly Record' sheet to be editable in case mistakes were made entering the information, but the 'Print' sheet shouldn't be anything more than references; I don't want it to be edited at all.

Summary/problem: The problem I have is figuring out how to add/remove cells/rows automatically from the sheets when adding/removing employees. Also I'm not sure how to copy the result of formulas to the 'Weekly Record' sheet from the 'Submit Info' sheet when the numbers are originally entered. I could of course just reference that cell on the Submit Info sheet, but that will change often and the information in the Weekly Record and Print sheets should maintain. Getting the Print sheet to reflect what's in the Weekly Record sheet is a no-brainer, so that's fine. Any direction here on what I need to research or a good ole example would be greatly appreciated! Thanks in advance!

[teylyn]

Hello,

This is a rather complex question. Break it down into smaller items, otherwise people here will be overwhelmed.

I think you don't need the Employees sheet at all. If you re-structure the Submit info sheet to include the day and the shift as fields for each line item, you can build reports with pivot tables. Your data entry table should have the fields:

Date | Shift | Position | Employee | Hour | Value

Then you can create pivot reports, breaking this table down into individual weeks, shifts, positions, whatever. The pivot table will automatically only contain the employees for the selected time frame, so there is no need to keep a list of employees somewhere else.