How add or delete parts a formula with relative references

[ludoviclalo]

Is there a way to add part of formula to multiple sheets are once where cell references are relative and may not be adjacent to each other?

For example, I start with November totals--
Sheet (Nov Totals): D3=SUM('Nov Data'!Z2:Z31)
Sheets (Cumulative through Nov): D3=SUM('Oct Totals'!D3,'Nov Totals'!D3)

and end with September totals--
Sheet (Sept Totals) D3=SUM('Sept Data'!Z2:Z31)
Sheet (Cumulative through Sept): D3=SUM('Oct Totals'!D3,'Nov Totals'!D3,'Dec Totals'!D3,'Jan Totals'!D3,'Feb Totals'!D3,'March Totals'!D3,'April Totals'!D3,'May Totals'!D3,'June Totals'!D3,'July Totals'!D3,'Aug Totals'!D3,'Sept Totals'!D3)

The problem is that it is tedious to manually add a new month to 522 different different formulas that capture data for the prior months. Is there a way to use find/replace, vba, or something to mass change the cumulative formulas so that I can add a month and have it applied to the entire sheet for that month?

Basically, change this D3=SUM('Oct Totals'!D3,'Nov Totals'!D3) to this D3=SUM('Oct Totals'!D3,'Nov Totals'!D3,'Dec Totals'!D3) for formulas in 522 cells at one time? This would be simple if D3 was static and the cells were adjacent, but they are not. The formula are the same or all 522 pieces of data, except for the cell references.

Thank you.

 

[offthelip]

the way I would do this is to write all of the equations using VBA, To assist this I would set up a hidden or very hidden worksheet that controls what is written to the workbook.
Column A I would put the address where the equations need to go on the culmulative sheet
Column B is the start of the equations ( (without the equals sign)
Column C is the end of the equation ( probably just a closing bracket)
In Column F I have put the months that are needed

Sub test()
tt = Chr(34) 'double quote
st = Chr(39) ' single quote
With Worksheets("Control")
lastmonth = .Cells(Rows.Count, "F").End(xlUp).Row
mon = .Range(.Cells(2, 6), .Cells(lastmonth, 6))
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
eqns = .Range(.Cells(2, 1), .Cells(lastrow, 3))
End With
With Worksheets("Cumulative through Sept")
 For i = 1 To lastrow - 1
 colno = Range(eqns(i, 1)).Column
 rowno = Range(eqns(i, 1)).Row
 eqn = "=" & eqns(i, 2)  ' put in start of equation
  For j = 1 To lastmonth - 1
   eqn = eqn & st & mon(j, 1) & " Totals'!" & eqns(i, 1) & ","   ' add all the months to the string
 
  Next j
  eqn = Left(eqn, Len(eqn) - 1) & eqns(i, 3) ' remove last comma and add end of equatrion
  .Range(.Cells(rowno, colno), .Cells(rowno, colno)) = eqn
Next i
End With
End Sub

Here is a snap shot of the control sheet:

 

[ludoviclalo]

Thanks for trying to help and your prompt response, but either I am missing something (most likely) or the code is off. I forgot to mention that I am not fluent in VBA, but I know the "basics" after having copied/pasted/changed (what I could figure) a lot of other's code.

I added the attached "Control" sheet with data in columns in A, B, C, and F as I understood the guidance. I did not make any changes to the code. I received a Run-time '1004': Application-defined or object-refined error" when I ran the code. Debug highlighted this row: .Range(.Cells(rowno, colno), .Cells(rowno, colno)) = eqn.

I saw your comments in the code. I don't know if these simply explained parts of the code (which I think they do, since the code should be pulling data from the Control sheet) or if they were were instructions for me to make changes to the code. Please clarify if I needed to change the code per the comments and, if so, please explain in more detail I need to change if I do.

Thank you.

 

[ludoviclalo]

It looks like my images did not upload, so I re-followed your guidance: created a new sheet called Control, copied/pasted your code to a new module. I then created columns A, B, C, and F with data. I received a different run-time error. Hopefully, the images will upload this time.

Sorry to confuse things, but I received the error 13 in these images when I initially tried to follow your instructions. I received the 1004 error mentioned earlier after I created the Control sheet with data and copying/pasting your code to a new module, when I received the error I made 1-2 changes to your code (trial and error changes) but then reversed them (I think). Regardless, error 13 was received immediately running your untouched (just copied/pasted/ran) code.

Thank you.

 

[offthelip]

If you look at my control sheet you will see that you have missed the bracket after SUM,
also your months are all in one row, they need to be one month in each row starting in row 2
LOOK AT MY CONTROL SHEET. Start by copying it exactly, then add "Dec" in F4,
Then try adding another equation in D5 by putting "D5" in cell A4 and "COUNT(" in B4 and ")" in C4

 

[ludoviclalo]

If you look at my control sheet you will see that you have missed the bracket after SUM,
also your months are all in one row, they need to be one month in each row starting in row 2
LOOK AT MY CONTROL SHEET. Start by copying it exactly, then add "Dec" in F4,
Then try adding another equation in D5 by putting "D5" in cell A4 and "COUNT(" in B4 and ")" in C4

Sorry about my apparent lack of attention to detail and/or "idiocy." I could not see your entire attachment due to browser blocking. Using a different browser now. Needless to say, seeing the entire attachment makes a big difference. It all makes sense or more sense now. I did a couple of tests and your code is exactly what I need. I will need to copy and relabel the non-control sheet names many times in your code to handle sheets for other periods, but that's fine instead of having to make 522 changes to each sheet manually for dozens of sheets!

Thank you very much for your time, sharing your knowledge, and promptness.

 

[offthelip]

I am glad to have helped.
If the equations are the same on every sheet you could put an extra loop in to go through a list of worksheets.