better way than consolidating?

[jenpier15]

I have been manually using the consolidate function on a cell by cell basis.
There has to be a better way to achieve the following:
I have the same worksheet format for all of the sheets I am working with, for ease of use.
there is one workbook per month, with one sheet per week and an additional sheet for the sum of all weeks for that month.
There is another workbook with sheets for the summed data for the month then all 12 of these are summed up for YTD.
monthly sheet (repeated for all 12 months):

YTD sheet (view 1). Jan would contain the same (consolidated/summed) data as Jan Totals above.

YTD view 2 (to see the YTD sheet) this contains all 12 monthly totals & consolidates as data is added (manually for now)
I am trying to set up a less labor intensive system (both in creating for the new year as well as not having to cut/past to the YTD on a monthly basis.
in creating new worksheets for the year, I am having to correct previous mistakes and do it cell by cell. there has to be a better way!!!
thoughts/suggestions please?

 

[severynm]

I have the same worksheet format for all of the sheets I am working with, for ease of use.

This is huge - you just saved your future (and I suppose current self) a bunch of time by doing this.

Most of what you want can be accomplished through some combination of 3D References (this is also a nice video explanation) and an aggregation of some sort. So, for your first workbook aggregating the weeks into the month, your formula in B2 on the Jan Totals worksheet might look something like =SUM('01 Jan:29 Jan'!B2) (assuming SUM is the aggregation you want). The same thing applies to the yearly aggregation workbook. I'd manually copy the results from each month into the year workbook, then in the 2023 Total worksheet, repeat the same calculation =SUM('Jan 2023:Dec 2023'!B2).

I am trying to set up a less labor intensive system (both in creating for the new year as well as not having to cut/past to the YTD on a monthly basis.

If you want to do this, the solution is going to get a bit more complex. I'd recommend pulling from each month workbook via Power Query. Data ribbon tab → Get Data → From File → From Excel. You can either load each of these months to the worksheet to an Excel table on a new worksheet, where you can still use the 3D reverences to aggregate, or you could perform the merges and aggregation within PQ and only put the final yearly total table to the worksheet.