• Status: Solved
• Priority: Medium
• Security: Public
• Views: 338

# Total worksheets based on Date

I've named all worksheets in a "dd-mmm-yyyy" format
there is a section in within every worksheet that I want to do a YTD total on.  The total should be the current sheet as well as all worksheets within the current year less than the date of the current worksheet. It's kinda hard to explain.
I've Attached a copy of my worksheet.  the area that needs to be YTD is in Orange.
YTD-Totals.xlsx
0
Jenedge73
1 Solution

Hi Jenedge73,

will you pls explain in brief what to total. I am on sheet 6-apr2013 cell C17. in that Cell you want to add from this sheet as well as from other sheets.

I understood you want total for 6apr2013 + date older then this. correct me if I am wrong.

Thanks
0

Author Commented:
I want it to sum all c35 in worksheets with a date less that the current worksheet date in C17.
does that help?
0

Hi Jenedge73,

it required VBA Coding  as per my understanding & I don't do VBA, so can click on request attention button if you want to.

Thanks & Good Luck
0

Commented:
Here is a working code for your problem. It should be pasted in ThisWorkbook section of VBA. When you run SumItAll, all values from C17 to S32 on all sheets with a valid date as a name will be set to \$0.00.

Then each sheet with a valid date will be compared to the other and the appropriate values will be added one at a time. This is not the most effective way of doing this, so the calculation should not be done each time Excel does an automatic recalculation. Also, make sure that each person stays on the same column from sheet to sheet, as there are no checking of the names in row 3.

Finally, it's getting late, so I have not had time to debug this code thoroughly. Use it at your own risk and to control to see that the numbers really add up.
``````Sub SumItAll()
Const NumbersOfDataRows = 16
Const FirstSumRow = 17
Const FirstSourceRow = 35
Const FirstColumnToSum = 3
Const LastColumnToSum = 19

Dim CurrentSheet As Worksheet
Dim DataSheet As Worksheet

Dim CurrentDate As Date

Dim ValidDate As Boolean

Dim SplitDate As Variant

For Each CurrentSheet In ThisWorkbook.Sheets
ValidDate = SheetName2Date(CurrentSheet.Name, CurrentDate)
If ValidDate Then
'Zero the totals
For r = 1 To NumbersOfDataRows
For c = FirstColumnToSum To LastColumnToSum
CurrentSheet.Cells(r + FirstSumRow - 1, c).Value = 0
Next c
Next r

For Each DataSheet In ThisWorkbook.Sheets
If ValidDate Then

'Dates on both sheets have been found valid
'Date on DataSheet is within interesting range
For r = 1 To NumbersOfDataRows
For c = FirstColumnToSum To LastColumnToSum
CurrentSheet.Cells(r + FirstSumRow - 1, c).Value = _
CurrentSheet.Cells(r + FirstSumRow - 1, c).Value + _
DataSheet.Cells(r + FirstSourceRow - 1, c).Value
Next c
Next r

End If
End If
Next DataSheet

End If
Next CurrentSheet

End Sub

Function SheetName2Date(ByRef SheetName As String, ByRef SheetDate As Date) As Boolean
On Error GoTo errorhandler

Dim dd As Integer
Dim mm As Integer
Dim yyyy As Integer
Dim SplitDate As Variant

SplitDate = Split(SheetName, "-")

dd = SplitDate(0)

Select Case SplitDate(1)
Case "Jan": mm = 1
Case "Feb": mm = 2
Case "Mar": mm = 3
Case "Apr": mm = 4
Case "May": mm = 5
Case "Jun": mm = 6
Case "Jul": mm = 7
Case "Aug": mm = 8
Case "Sep": mm = 9
Case "Oct": mm = 10
Case "Nov": mm = 11
Case "Dec": mm = 12
End Select

yyyy = SplitDate(2)

SheetDate = DateSerial(yyyy, mm, dd)
SheetName2Date = True

Exit Function

errorhandler:
On Error GoTo 0
SheetName2Date = False
End Function
``````
0

Commented:
You might consider using a user-defined function with a worksheet formula like:
=YTD(C35)

Put the following code in a regular module sheet:
``````Function YTD(CellToSum As Range) As Variant
Dim ws As Worksheet
Dim CurrentDate As Date
Application.Volatile
CurrentDate = CDate(CellToSum.Worksheet.Name)
If IsDate(CellToSum.Worksheet.Name) Then
CurrentDate = CellToSum.Worksheet.Name
Else
YTD = "#DATE!"
Exit Function
End If
For Each ws In CellToSum.Worksheet.Parent.Worksheets
If IsDate(ws.Name) Then
If CDate(ws.Name) <= CurrentDate Then YTD = Application.Sum(YTD, ws.Range(CellToSum.Address))
End If
Next
End Function
``````

The attached workbook shows how it might work. See the formula in cell C17 on 6-Apr-2013 worksheet.

YTD-TotalsQ28170943.xlsm
0

Author Commented:
Fantastic Thanks
0

Mr. byundt,

what if I don't want to go for date based sum i.e. sum of all sheet cell excluding current sheet.

=YTD(C3) which calculate sum of all sheet excluding current sheet.

Cell Sum

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.