Forum Discussion
Creating Duplicate pages in an excel workbook
Oh, my! The issue is that it's not mere "formatting" change that I have in mind. By "formatting" I am referring to the nicely laid out form that you ask employees to sign, verifying hours worked. That format is perfectly fine. But it's an output document.
It's an output document that is being used to dictate how you get input. THAT'S the thing I'd change. Excel is really really REALLY good at taking raw data and formatting it for a whole range of outputs. You're being asked to work with an output document as the receptor of the raw data, and are looking for ways to tweak that. But it isn't a good approach to begin with. You've heard the expression "putting lipstick on a pig"?
And, sadly, the history you recount is a big part of why you are where you are. "We've always done it this way [on paper]; it has worked just fine. We hereby give you permission to take this fancy newfangled automated ledger sheet and convert the old paper forms into it, but, please, keep the old process going just as it was. We'll let you use Excel to do the adding and multiplying, but by no means should you monkey with the process that has worked so well with paper and pencil."
You know something? Excel can do more than fool-proof adding, multiplying, dividing and subtracting. But to use it to its fullest extent, and really achieve less error-prone results, you will need to be open to reconsidering how you collect and store the "raw data"......it's not a problem producing the same output, but do you really really REALLY just want to use Excel because the result will be printed by a computer rather than contain hand-written numbers?
I'm going to spend some time to create a little bit of a demonstration of what could be, but not a lot, since it's not clear that your management is open to actually improving the process if it means changing the process.
If it's the construction business that you're in, you might explore how they'd feel if, upon getting a new power gizmo to do a job, the customer allowed them to use it, but without actually plugging it in. If it were a drill, "you can use it because the bits are sharper than our old one, but you need to turn the whole thing by hand 'because that's the way we've always done it.'"
mathetes Hahaha! You nailed it! I work in the Construction Industry and they are not very willing to change with the times. I am fortunate that my direct boss embraces technology and my ability to make our company more efficient but above him, we have to follow their rules. That being said, I am very excited to see what you come up with and I will definitely make a pitch to the union to try this fancy new technology! I truly appreciate your time and efforts!
OK, so as I got started I realized I had a few more questions. Specifically about how the data are collected in the first place.
Does each person record hours individually and daily? (In other words, where and how is the "raw data" for how many hours John Jones has worked, and on which project....where, when, and how is that captured?
A couple of months ago I helped a firefighter in New York create an attendance and training tracking spreadsheet. He had been charged with keeping track of folks who came to work on a Covid-19 related endeavor, and had taken a paper process and converted it to Excel, but was just putting "X" into boxes and looking for a way to count them. We ended up changing the process altogether, so that he noted the date, the name, the shift (Day or Night; but not hours per se) and the location [comparable to your project]. This was all entered by him or an associate each day into a single Excel table. We then created weekly summary reports.
It's something like that that I have in mind for you. Creating a single Excel table into which you enter daily hours per person per project. It's very easy from that to produce the nicely printed output.....
But if you (or an associate) just enter the hourly data once a week, taking it from some other source, that would be helpful to know.
mathetes Each employee has a time-logging app on their phone. The app is equipped with each of our jobsites. The employee logs into the jobsite they are currently working on. Some days they might be at 4 different jobsites, some days they might spend their entire day at one jobsite. The accounting department prints the logs of all of this information on a weekly basis, gives it to me, and I input that information into this document for the Fund Department.
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.