Running Totals =sheet!cell

Microsoft Excel 2003 (full product)
July 7, 2010 at 15:23:46
Specs: Windows 7
I am doing a 365 day template, I am also needing to do a running total for every sheet. I have already created the sheets off of the main template and named them mm/dd. What I need to do is put in a code or something to make each sheet recognize the previous total without having to manually input each cell the previous sheets name.


See More: Running Totals =sheet!cell

Report •


#1
July 8, 2010 at 07:28:35
If I understand this correctly....
You have sheets for each day named 07/06, 07/07 and 07/08 and you need sheet 07/07 to recognize 07/06 as the previous sheet and include a number from a specific cell in its calculation?

If you change the sheet names to something sequential like 20100706, 20100707 and 20100708 you can identify the previous sheet in a cell formula...

The formula is:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1

If you paste this in sheet 20100707 cell A2 for example, you will see the cell value of 20100706 as a number. Together with the functions "Address" and "Indirect" you can get the subtotal from the previous page into the current page.

There is also a VBA solution to this....


Report •

#2
July 8, 2010 at 07:42:49
Thank you...I will keep looking for the VBA solution.

Report •

#3
July 8, 2010 at 11:51:27
Hi,

Regarding your worksheets you say: named them mm/dd
but this cannot be correct as "/" is not allowed in worksheet names.

If your worksheets are named mm_dd, you can use this macro:
(If the worksheet names are slightly different, you can modify the code)

There are two lines where you can set the address of the cell to hold the formula for the prior day's running total and the address of the cell containing the running total.

Option Explicit

Sub GetTotals()
Dim strWsNameToday As String
Dim strWsNameYesterday As String
Dim dtToday As Date
Dim strAddrYesterday As String
Dim strAddrToday As String
Dim strFormula As String
Dim m As Integer
Dim d As Double

'setup cell address of cell containing the daily total
strAddrYesterday = "A34"
'setup cell address of cell to hold formula for yesterday's total
strAddrToday = "A33"

'loop through each day's worksheet
'loop months
For m = 1 To 12
    'loop days - uses the day before the first day of next month
    For d = 1 To DateSerial(Year(Now), m + 1, 1) - 1
        'create todays date
        dtToday = DateSerial(Year(Now), m, d)
        'skip 01/January
        If dtToday <> DateSerial(Year(Now), 1, 1) Then
            'create today's worksheet name
            strWsNameToday = _
                Format(Month(dtToday), "00") & "_" & _
                Format(Day(dtToday), "00")
            'create yesterday's worksheet name
            strWsNameYesterday = _
                Format(Month(dtToday - 1), "00") & "_" & _
                Format(Day(dtToday - 1), "00")
            'create formula eg "='01_01'!A34"
            strFormula = "='" & strWsNameYesterday & "'!" & strAddrYesterday
            'place formula on today's worksheet
            Worksheets(strWsNameToday).Range(strAddrToday).Formula _
                = strFormula
        End If
    Next d
Next m
End Sub

Also as you are dealing with 365 worksheets, that you can edit or enter data or formatting to all of them at once by grouping the worksheets first.
Click the first daily worksheet in the name tab list and hold down the Shift key and click the last one. Changes made to the visible worksheet will be applied to all the selected group of worksheets.

To ungroup either click on the tab name of an ungrouped worksheet or right-click on a the grouped worksheets names and select Ungroup Sheets.

Regards


Report •

Related Solutions

#4
July 8, 2010 at 16:31:13
Thank you very much! My tabs are actually named "JAN1" and so on. I am going to try this.

Thank you again.
Lyne


Report •

#5
July 9, 2010 at 05:23:30
Hi,

I simplified the macro and changed the worksheet names to Jan1 etc.

As before the cell containing the day's total and the cell containing the formula to link to the previous day's total are set on two lines near the start of the code.

In simplifying the code, note that the code will be one day short if used in a leap year. Feb29 will be included, but the formula will not get added to Dec31

Sub GetTotals()
Dim strWsNameToday As String
Dim strWsNameYesterday As String
Dim dtStart As Date
Dim strAddrYesterday As String
Dim strAddrToday As String
Dim strFormula As String
Dim n As Integer

'setup cell address of cell containing the daily total
strAddrYesterday = "A34"
'setup cell address of cell to hold formula for yesterday's total
strAddrToday = "A33"

'loop through each day's worksheet
'set start date
dtStart = DateSerial(2010, 1, 1)
'loop through the year - Jan 01 will not be included
'because n is added to the start date
For n = 1 To 364
    'create today's worksheet name
    strWsNameToday = Format(dtStart + n, "mmm") _
                & Format(dtStart + n, "d")
    'create yesterday's worksheet name
    strWsNameYesterday = Format(dtStart + n - 1, "mmm") _
                & Format(dtStart + n - 1, "d")
    'create formula eg "='Feb23'!A34"
    strFormula = "='" & strWsNameYesterday & "'!" & strAddrYesterday
    'place formula on today's worksheet
    Worksheets(strWsNameToday).Range(strAddrToday).Formula _
                = strFormula
Next n
End Sub

Regards


Report •


Ask Question