Ongoing Total Formula

victoriapowers

New member
Joined
Apr 15, 2016
Messages
6
Reaction score
0
Points
0
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)

Thank you in advance for your time.
 

Attachments

  • excel guru.xlsm
    118.6 KB · Views: 15
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.
 
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
 
@ 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.
 
Back
Top