Ok, so I administrate a database that is set up for time reporting. However, it gets very poor support for, of all things, political reasons. I am not even close to having sufficient mastery over Access to create a better database. However, I am pretty good with excel. I thought I might list out my requirements and see if someone could think of a good way to set this up.
Employees have 2 attributes, a name, and a username. Employees are also a member of a team, but they do not have to be associated with the team in any formal sense.
Teams have accounts. These accounts are what the employees report their hours to. There are about 12 different teams and 300 some accounts. Some very seldom see time reported to them.
Each week the employees would have to go into the sheet and report their time. In order to encourage timely reporting, I want them to do it on a weekly basis. They should report at least 40hours each week, if they are on vacation, or sick they should still report.
It seems like it would be pretty easy to make a sheet that had drop downs for all the employee names in one column, authenticated by a list kept in a hidden tab. Additionally, I could divvy up account options by team. However, I'm not sure how I would have them pick team X and then have a drop down of all the accounts for team X.
After selecting their name, their team, and their account, Then there would just be 4 or 5 columns each month depending on how long the fiscal month is for them to report time to. I would need to send an email to all of the individuals who have forgotten to report for a given week, which could be pretty easily generated with some quick sorting each week.
However, here is the rub there are about 200 employees. There will often be multiple people that need the sheet at the same time. I have heard that there is a lot of difficulty with setting up shared workbooks, but it seems like, especially if I lock the whole sheet down, except for where employees report time, there is no way it could get messed up given the simplicity of the thing.
Suggestions? ideas? Sympathy for someone who can't set up access to do what he needs? Thank you in advance for taking the time to read my post, fellow excel nerds.
Employees have 2 attributes, a name, and a username. Employees are also a member of a team, but they do not have to be associated with the team in any formal sense.
Teams have accounts. These accounts are what the employees report their hours to. There are about 12 different teams and 300 some accounts. Some very seldom see time reported to them.
Each week the employees would have to go into the sheet and report their time. In order to encourage timely reporting, I want them to do it on a weekly basis. They should report at least 40hours each week, if they are on vacation, or sick they should still report.
It seems like it would be pretty easy to make a sheet that had drop downs for all the employee names in one column, authenticated by a list kept in a hidden tab. Additionally, I could divvy up account options by team. However, I'm not sure how I would have them pick team X and then have a drop down of all the accounts for team X.
After selecting their name, their team, and their account, Then there would just be 4 or 5 columns each month depending on how long the fiscal month is for them to report time to. I would need to send an email to all of the individuals who have forgotten to report for a given week, which could be pretty easily generated with some quick sorting each week.
However, here is the rub there are about 200 employees. There will often be multiple people that need the sheet at the same time. I have heard that there is a lot of difficulty with setting up shared workbooks, but it seems like, especially if I lock the whole sheet down, except for where employees report time, there is no way it could get messed up given the simplicity of the thing.
Suggestions? ideas? Sympathy for someone who can't set up access to do what he needs? Thank you in advance for taking the time to read my post, fellow excel nerds.