How to create a report based on a dynamic list of sheets?

Copper Contributor

I have a list of individual reports, hour reports of employees. When I export this list from our system, it places each individual's information in their own sheet within excel. I'm trying to use each individual sheet to feed into a master list of hours and other data for a management report.

 

Each sheet is formatted similarly, but some employees may have additional rows on their hour reports for special duties performed that need to be captured separately. Can anyone help me figure out the best way to run this report from multiple sheets onto a single view of hourly reports? Ideally, this would be able to work on any number of employee sheets as employees transfer in/out of the section.

 

Thank you!!

9 Replies

@AustinAdams 

 

Excel has some excellent tools for summarizing data (as you know)

 

It works most readily, most easily, most seemlessly, when you take those summaries from a single database (or maybe from two or more that can be joined by key data elements such as SSN (in the case of employee databases))

 

What makes it harder is when you're working from separate sheets, such as you're describing here.

 

But it sounds like those separate, individual sheets come FROM a single data source. So I want to ask, before you solve this the hard way, is it possible to go back in the sequence of events here, to create this "single view of hourly reports" from that original source of data?

 

[FWIW, during my career, I was the director of the HR/Payroll database for a major corporation, so I've "been there, done that." One of my specialties was being able to use SQL to extract data from an IBM DB2 database, into Excel, where I could use Pivot Tables and other Excel tools to summarize raw data into the form of useful management reports. There were occasions when the IT professionals were relying on brute force programming routines to summarize data, and I was able to use built-in Excel functions to accomplish the same results, faster, more reliably.]

Thanks for the response @mathetes,

 

Sadly, I do not have access/permissions to the source DB and am stuck consuming the reports as they are exported.

@AustinAdams You can sum all worksheets automatically, but you many need to add a dummy one at the left and right sides so you don't need to edit the formulas each time:

https://exceloffthegrid.com/how-to-sum-the-same-cell-across-multiple-worksheets/

 

@AustinAdams 

 

One of your opening sentences, in your first post,  was this "When I export this list from our system, it places each individual's information in their own sheet within excel," which suggests you at the very least have access to run this set of reports, and (implicitly) access to the data that is involved.

 

So I'll grant that you may not have the kind of master access that I had as the overall DB director, but I was able to grant views to divisional or location HR professionals to certain views of data they needed. It sounds like you do have that kind of access, albeit somewhat limited.

 

Based on what you've said, I'd encourage you to go to whoever it is that manages the whole system to see if you could be granted some kind of export of this very same data, just in a different format that would enable the kind of summary you're looking for. It would be a lot easier (and, importantly, less error prone) than reconstructing the raw data from the individual and variable sheets.

@mathetes 

 

It's a submit form. I select the dates, click generate report and it comes back as HTML pages. There is an export button that I can click that then exports each of these pages to an excel document with all the sheets. That's my limited access to the data, no way to interface directly with the data outside of the form they provide. The "system" being the time reporting application provided.

@wsantos 

 

I need to pull the hours for each individual and show a row per person with their hours. I wish I only needed a SUM of them all :) I have an ever-changing list of sheets and need to turn data from each sheet into a single row on 1 master sheet for ease of viewing, and ideally have that master list change based on the number of sheets exported. Hope that helps clarify my problem and thanks for the response!

@AustinAdams A few things that come to mind (in no specific order):

1 if the employee list doesnt' change much, you could SUMIF across the worksheets

2 use a powerquery to merge all data together, then just use a pivottable. Not sure how you'd handle multiple dynamic worksheets there though.

3 try a webquery  to pull the data from the webpage directly (if this is not coming from a regular app), and see what you get in return. 

4 build a macro to loop through the worksheets and copy/paste the data in a new consolidated spreadsheet - this could be the easiest one

 

good luck

@AustinAdams 

 

OK, since it appears we're stuck with what you get, let me ask if you could post a couple representative samples (devoid of any actual names, SSNs,, etc., of real people) so that we could see if we can come up with a creative solution.

 

Use examples that illustrate the range of rows, different data, and make sure it's clear which data you need to extract and summarize.

 

And please include information on which version of Excel you're working with. The most recent updates to the Microsoft 365 subscription versions have some functions that might come in handy.....

@AustinAdams , can you upload a sample with a few sheets (making sure that all confidential information is removed)?