Summarizing months in 1 sheet, from weeks across multiple sheets

Hi.

I'm having problems with the last step of finishing a weekly rota consolidation into a monthly summary for a year. Basically, each sheet is a week's rota, with all the fomulas and information involved. The info from these weeks are combined into monthly summaries on a single (year) summary sheet (with its own formulas), with each month having its own table, vertically.

Every week (sheet) has an idential layout with exact cell refs, but each monthly summary is a combination of several weeks. I have the first month completely set up and working fine, but I can't think of a way to replicate the month summary down the sheet for the following months. I basically have no reliable way for the worksheet to understand that every "table" I go down, that I want the sheet references to change. It just moves all the row refs down.

I would imagine there's a way to replicate the formulas in the first summary table to apply across consecutive sheets, but I can't figure out a way to do this (without manually changing the sheet refs for each column header).

Any ideas how I could accomplish this?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I attached a quick mock-up of the document. Very simplified, but fundamentally attemping the same thing.

You can see the first completed summary sheet as Jan, and further down that sheet, you can see Feb (empty). I basically need the same data format that comes from set 1 (sheet 2) to jan, for feb from set 2 (sheet 3)
If I try to copy/paste the format from the Jan table, it simply gives me the same pattern of data, but x rows lower from set 1.
I need a way where I can transfer the same formatting from those cell refs, but from sheet 3 (set 2) instead, and so forth.

Let me know if you have any questions.

Seems like nobody in this forum is willing or capable of helping. What a shame.

Originally Posted by JeteMc

Let us know if you have any questions.

I've had a chance to look at the very nice setup you made in response to my query and I appreciate the suggestion with using a pivot table. The indirect function, as useful as it would be for relatively simple repetitive sheet designs, isn't suited to solve my issue.

What I found when experimenting with indirect, is while it's better for straight copy-paste of smaller functions, it increases the complexity of longform functions since as far as I can tell, you still need to individually edit the "source" for the indirect after copy-pasting. (Changing the reference cell with the sheet name within the function for each instance)

As an example of one of the longer functions below, you can see why this wouldn't be suitable:

=SUM(('test 1'!AX7+'test 1'!AX33+'test 1'!AX59+'test 1'!AX85+'test 2'!AX7+'test 2'!AX33)-(IF('test 1'!D7="TM",('test 1'!G7-'test 1'!E7),"0"))-(IF('test 1'!J7="TM",('test 1'!M7-'test 1'!K7),"0"))-(IF('test 1'!P7="TM",('test 1'!S7-'test 1'!Q7),"0"))-(IF('test 1'!V7="TM",('test 1'!Y7-'test 1'!W7),"0"))-(IF('test 1'!AB7="TM",('test 1'!AE7-'test 1'!AC7),"0"))-(IF('test 1'!AH7="TM",('test 1'!AK7-'test 1'!AI7),"0"))-(IF('test 2'!J33="TM",('test 2'!M33-'test 2'!K33),"0"))-(IF('test 2'!P33="TM",('test 2'!S33-'test 2'!Q33),"0"))-(IF('test 2'!V33="TM",('test 2'!Y33-'test 2'!W33),"0"))-(IF('test 2'!AB33="TM",('test 2'!AE33-'test 2'!AC33),"0"))-(IF('test 2'!AH33="TM",('test 2'!AK33-'test 2'!AI33),"0"))-(IF('test 2'!AN33="TM",('test 2'!AQ33-'test 2'!AO33),"0")))

Unless I'm missing something about the implementation, it seems to require me adding more inputs than before.

The document is a 4 week rolling rota, with the summary sheet taking the weekly summed data and calculating it to calendar months. Naturally because there isn't 28 days in every month, some months I need to take manual data away, or add manual data from other sheets. This is where it gets really messy.

My current method involves copy pasting a "completed" sheet format into cell C5 for example. I then manually change the sheet name for each reference (which I change to 1,2,3 for ease of typing). Then any variable part of (extra or insufficient days) I manually input, which usually takes me a few minutes. I then drag this finished fomula down for every staff member, then copy-paste C5 into D5, then manually shift the reference 1 clumn over e.g AB6 > AC6. I also shift any variable over, e.g H > TM. Once I fully format the first cell, the rest are relatively simply copy-paste, as the day references all match. The real difficulty comes when finishing one month, and then going to the next month. I have to copy the C5 formula, and then post it in (for example) C20.I then start the process over, changing the sheet reference, then adding/removing day snippets, copying down, then transferring across, and so on.

My errors pop up because of the sheer number of very simple, but manual reference changes.

What I'm looking for is some way to take, for example the function in C5, and copy it to C20, but in a way where, within the strings, the reference changes automatically to the next sheet. That way, all I'm changing is individual days. With the indirect suggestion, I'm essentially having to do the same thing, and manually change the indirect cell ref for each new sheet.

I appreciate the suggestions though, and please, let me know if I'm mis-interpreting the indirect formula. Making pivot tables isn't an option.