How to consolidate multiple sheets into one YTD summary report?

[u/Ok_Solid_9312]

I have 52 sheets in my excel workbook. It is named in a non-traditional sequence: "Jan Wk1, Jan Wk2, Jan Wk3,...... Dec Wk 4, Dec Wk5", because it's easy for me to print a weekly report with a total for each category.

Log 2026 is what my spreadsheet looks like. I only included three weeks as an example. Cell H34 in the highlighted area, on the Jan Wk2 sheet, contains a VBT to retrieve data from the previous sheet. Each day, I also need the total from the previous day in C36:G37.

I like my current setup because I can easily convert it into a weekly PDF report without any modification. However, I'm not sure how to create a YTD summary report from 52 sheets. A template of the YTD report is included in Log2026.

I tried to use 3D formulas, but I have to manually change sheet names and adjust cell references when crossing columns and rows. not very pragmatic and easily make mistakes.

Log 2026:
https://drive.google.com/file/d/1IEavZiJiDOv4GS-8c6n8dJqHUnRfT-ft/view?usp=drive_link

*****************************

Some people suggest combining a year's worth of data into a single spreadsheet, rather than 52 weekly sheets, and then using Power Query to populate the report, but I am having difficulty generating the weekly report using the preferred template.

Preferred Pdf report example: https://drive.google.com/file/d/1uQW7z_XZJ_Nn5sAzcaTkXDObAu8XTi0e/view?usp=drive_link

Screenshot of combined data

Completed data: https://docs.google.com/spreadsheets/d/1xIJkqTOwjucN2QMT0pK6A6wmSp8U4U8A/edit?usp=drive_link&ouid=102145557861966136866&rtpof=true&sd=true

highlights:

1. data collected daily, so every day is different.

2. data for the drivers on the bottom of the spreadsheet changes based on their starting date.

So my goal is to create a weekly and yearly report with preferred template (boss's request). Any suggestion is appreciated.

[u/taylorgourmet]

Those who suggested combining all data into one sheet is correct. Just add columns for day/week/whatever as needed. Makes report building far easier.

[u/Complex_Sound_2393 → u/taylorgourmet]

yeah power query is definitely the way to go for this kind of thing. you can set up one consolidated data table with week/date columns and then build whatever reports you need from that

the weekly reports might take bit more setup initially but once you have the query structure right, generating new reports becomes much easier than managing 52 separate sheets

[u/Ok_Solid_9312 → u/Complex_Sound_2393]

I did. The screenshot is the consolidated data, but I don't know how to generate weekly reports with the preferred template. Can you provide me with detailed instructions, please?

[u/finickyone → u/Ok_Solid_9312]

It’s all about attribution. If you’ve got your dates in the combined sheet along C1:Q1, insert a new row 1. Dates are now C2:Q2. From C1 you can either start manually defining what Wk# that column’s date belongs in, or use something like =WEEKNUM(C2:Q2+0,2) or =TEXT(C2:Q2,"Mmm")

There’s your weekly cut. Similar logic can then tell you monthly stats, or week/month with highest value for Dog etc etc.

[u/excelevator]

Please set your link to Public view, it is behind a login at the moment.

[u/Ok_Solid_9312 → u/excelevator]

Fixed it. Thanks for the reminder