Forum Discussion
Creating Duplicate pages in an excel workbook
I have attached a fully functional demo with private information edited out. Sadly, I am bound by the laws of powers higher than myself as to the format of these documents. Until I came along, these were done ENTIRELY by hand. I was granted permission to create a digital version that followed the guidelines of the original document. I currently have a functioning version aside from this document but I still find it to be inefficient as we do not always have hours on every jobsite every week ( yet I am still required to state those hours - or lack thereof) until the job is closed.
The formatting works thus: On the cover page: the jobsite, month, W/E and employee name are selected. This information auto-populates to the subsequent 5 potential weeks of each month. Then, on each weekly report, the hours logged on those pages auto-populate to the cover page and are thus tallied and compiled into the weekly/monthly total hours. I then must PRINT off each sheet for each jobsite and get signatures from each employee to verify their hours and then mail these documents into 'corporate'. I am not allowed to submit these electronically and each job and each week must be accounted for regardless of hours worked.
With that information in mind, I am totally open to any formatting changes you might have. Thank you! (Also to recap: The issue I am having is creating duplicate sheets within each week without losing substantial formatting) Each week could see upwards of 45 different jobsites and each one must be accounted separately)
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.