1. ## Ongoing Total Formula

I am looking for help regarding an ongoing total formula

I have a workbook with multiple sheets, and additional sheets being added daily.

Cell B126 will always have a number in it. I am looking for cell B127 to have a formula for an ongoing total, for example:

Sheet 1 (named Day 1) has the number 20 in cell B126
Sheet 2 (named Day 2) has the number 20 in cell B126, I would like cell B127 to add the totals of B126 from Day 1 + Day 2
Sheet 3 (named Day 3) has the number 20 in cell B126, I would like cell B127 to add the totals of B126 from Day 1 + Day 2 + Day 3
and so on....

I realize the standard =SUM formula will not work, as it would only provide the total amount from every sheet, where as I am looking for a running total based on Day 1, Day 2, Day 3, etc.

Workbook attached (called Excel Guru)

2. It should be possible to do this using VBA. You would need a piece of code that creates a suitable formula in B127 to calculate a new total. As its possible that the sequence of the sheets could be altered, I suggest that the value in B126 should be totalled for every existing sheet and this figure is placed in B127 of the new sheet and we then append +B126 to this to include whatever will be in that cell on the new sheet.
To do this you place the code in a Private Sub Workbook_NewSheet which is executed each time a new sheet is added to the workbook.

3. I would try putting this formula in B127 of "Blank to Copy"

=\$B\$126 + INDIRECT("'Day " & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,255) -1 &"'!\$B\$127")

and altering the code so the number to be used in the new sheet name is automatic. A missing day will screw up this formula.
Code:
```Sub CreateNewDay()
'
' CreateNewDay Macro
' Creates new tab for daily work log
'
' Keyboard Shortcut: Ctrl+Shift+D
Dim InputValue As Variant
Dim NewName As String
Dim i As Integer
Dim x As Integer

For i = 1 To ThisWorkbook.Sheets.Count
If Left(Sheets(i).Name, 3) = "Day" Then
x = WorksheetFunction.Max(x, Mid(Sheets(i).Name, 5))
End If
Next i

NewName = "Day " & x + 1

Application.ScreenUpdating = False
Sheets("Blank to Copy").Visible = True
Sheets("Blank to Copy").Copy Before:=Sheets("Blank to Copy")
Sheets("Blank to Copy").Visible = False
ActiveSheet.Name = NewName
If NewName = "Day 1" Then Range("B127").Formula = "=\$B\$126"
Application.ScreenUpdating = True

InputValue = InputBox("Please enter the date (mm/dd/yy):")
Range("B3") = InputValue

End Sub```

4. @ victoriapowers

The best ways to get ignored on these forums are:
1) cross posting without links and
2) not acknowledge replies to your questions

You appear to have mastered at least 50% of these.

5. Edit Mod
Post deleted - Coarse language

6. Still 50%

7. Originally Posted by NoS
Still 50%

Yet, you're still responding.....