Results 1 to 7 of 7

Thread: Ongoing Total Formula

  1. #1

    Ongoing Total Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    711
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    711
    Articles
    0
    Excel Version
    Excel 2010 64bit
    @ 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. #5
    Edit Mod
    Post deleted - Coarse language
    Please add links to any cross posts you might have created. Thank you
    Please read https://www.excelguru.ca/content.php?184
    Last edited by Pecoflyer; 2018-03-31 at 07:46 AM.

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    711
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Still 50%

  7. #7
    Quote Originally Posted by NoS View Post
    Still 50%

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

    Perhaps rethink your original hypothesis.

Tags for this Thread

Posting Permissions

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