@seg419
OK -- think of this just as an example, please.... I think, based on what you've said in that most recent note (about the employees logging into an app, registering hours per jobsite) that that data might be downloadable into a simple variation on the sheet I've created for input.
What I'm giving you is by no means complete. I can tell from what you'd created, however, that you're quite competent with Excel, various functions and linking them across different sheets in the workbook. What this is meant to demonstrate is how you can "table drive" a workbook such that it can basically consist of
Some "business tables" that contain basic data to be incorporated at various places in the process. In your case, there are in my demo the following tables:
- Employees--consisting of an ID#, Last Name, and First Name. This could be extended easily to include such things as DOB, DOH, Title, hourly pay rate or salary
- Project list--right now just the name, but easily extendible to include names of contacts, address, etc., for use on the output form
- an automatically generated list of Employee IDs, used for data validation on the input sheet
- I didn't do it, but if you go with this, we'd do the same with the Project list: create an automatically generated list of Projects for data validation
An input sheet, designed to capture your hours data right now at the most "granular" level--i.e., each entry will refer to hours worked by one employee at one location on one day.
- All that needs to be entered are
- date
- EE ID (drop-down list)
- Project (drop-down list)
- Hours
The other columns are generated automatically and are more for reference. They could be hidden columns, especially the one that calculates EOWeek. Its sole purpose is to enable easy selection of relevant data on the Output sheet
(I want to note here that it would be easy to create a different input layout, if what that App generates, or what you get on a weekly basis, is an entire row for each employee's hours at each job site. My example is at the level of finest detail (the highest granularity) because that also provides the greatest flexibility for later summaries by job-site, by employee, etc.)
The output sheet. And here in particular I want to emphasize that it's just a demo of what's possible. A word of warning...this makes use of the most recent dynamic array functions to extract from the input sheet the list of ID's and the hours for each location. So long as you're working with the most current Excel, you'll be fine. If they don't work, there are other solutions, but these are really powerful, so I'd highly recommend getting the most current if you don't already have it.
The output sheet right now will let you pick the project or jobsite there at the top left--I created a drop down list entry there: when you place your cursor in the box, you'll see the drop-down arrow appear immediately to the right. And once you've selected a different location, it will populate the sheet with the work that was done at that job site, that week, by employee, in employee ID order.
I've only entered a few rows of data in the input sheet, but invite you to enter more. I also invite you to add to the employee list -- new IDs and new names. What you'll see is that those can grow dynamically. It's easy, in other words, to bring new workers into the mix, even new locations.
What I've left for you to do is that master sheet. But I think you'll see that based on a single input worksheet, it's possible to use Excel to extract very specific data, as well as summary data should that be desired, from a single comprehensive input database.
