Advice for setting up a daily productivity workbook

[paulstan]

Hi

I am at the planning stage of creating a daily worksheet for staff to complete, either at the end of the day or the beginning of the next day. There are approximately 50 staff in total and I would like all 50 staff members’ to be able to input their daily work into a spreadsheet that only they have access to. There would also be Admin to have access to all sheets via a control panel. I want the ability to be able to add/delete staff members and to create pivot tables and graphs from the data the staff provide.

What I really want to know from the experts is the best way to do this, remembering that multiple staff may need to access the workbook at the same time and also that Admin may be analysing data for long periods of time. I know about the 'allow multiple users to edit the workbook' but fear that this may be too restrictive for my needs.

Would a possible option be for staff to use a single userform to enter their daily productivity and then have this save to one spreadsheet, which could then be manipulated accordingly for Admin/Team Leaders use. I could then put a password on the userform so data could only be input to the staff member who just logged in. Having a single user form would also allow for easy updates to the structure should further data be required by Team Leaders and also allow Admin to easily add or delete staff. This, however, wouldn't solve the problem of having lots of staff trying to access the user form at the same time.

An easier option may be to have staff have a stand-alone worksheet which they update on a daily basis, print off and hand to their Team Leader at the end of the week; the Team Leaders would then input the data to a master list. Unfortunately, this would mean double-handling, possibly long-winded and not really a preferred option.

I will be working on Excel 2010.

I have an ok understanding of Excel and am getting to grips with VBA. I just need a pointer in the right direction of the best way to approach this?

Many thanks

Paul S

 

[Dryver14]

Might be best creating a folder with master then a separate workbook for each employee in the same folder then collecting the data with a Macro at the end/beginning of the week

 

[paulstan]

Thanks for the reply. The problem I have with having separate workbooks for each employee is, I know my workplace only too well - 5 minutes after designing a workbook and distributing to 50+ employees, some bright spark would come up with "can we add another column..." - which would mean updating all the workbooks - unless a macro could do this automatically.

If I did look at the option you suggest, would the macro collect all data from the folder, irrespective if someone changed the allocated filename and how do I go about setting up 50 passwords and then having the macro collect data from the password-protected workbooks?

Thanks

Paul S