Solved

Total worksheets based on Date

Posted on 2013-06-28
7
294 Views
Last Modified: 2013-07-02
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
Comment
Question by:Jenedge73
7 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 39285362
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 Comment

by:Jenedge73
ID: 39285567
I want it to sum all c35 in worksheets with a date less that the current worksheet date in C17.
does that help?
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39285783
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:arildj78
ID: 39286091
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 DataDate 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
                ValidDate = SheetName2Date(DataSheet.Name, DataDate)
                If ValidDate Then
                    
                    'Dates on both sheets have been found valid
                    If (DataDate <= CurrentDate) And (DataDate >= DateSerial(Year(CurrentDate), 1, 1)) Then
                        '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

Open in new window

0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39286093
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

Open in new window


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

Brad
YTD-TotalsQ28170943.xlsm
0
 

Author Closing Comment

by:Jenedge73
ID: 39288447
Fantastic Thanks
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39293604
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now