Forum Discussion
How to create a report based on a dynamic list of sheets?
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/
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 , can you upload a sample with a few sheets (making sure that all confidential information is removed)?
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
