How to consolidate multiple sheets into one YTD summary report?
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
Completed data: https://docs.google.com/spreadsheets/d/1xIJkqTOwjucN2QMT0pK6A6wmSp8U4U8A/edit?usp=drive_link&ouid=102145557861966136866&rtpof=true&sd=true
highlights:
data collected daily, so every day is different.
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.
[link] [comments]
Want to read more?
Check out the full article on the original site