# Thread: Ongoing Total Formula

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)

Thank you in advance for your time.  Reply With Quote

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.  Reply With Quote

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```  Reply With Quote

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.  Reply With Quote

5. Edit Mod
Post deleted - Coarse language
Please add links to any cross posts you might have created. Thank you  Reply With Quote

6. Still 50%  Reply With Quote

7. Originally Posted by NoS Still 50%

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

Perhaps rethink your original hypothesis.  Reply With Quote

#### Tags for this Thread

excel formula, formula help, formula sum if #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•