Need consolidated information

I'm not sure that there's an easy way to do this, might be relegated to cut and paste, but I thought it was worth asking...

I have a spreadsheet from our booster club, in which they have kept each month of check register in an excel table in each of 12 different tabs (i.e. Jan15, Feb15, etc.).  Each table is obviously variable in length, and contains pretty much what you'd expect (check#, date, description, category, withdrawal, deposit, balance).  They would like to summarize the categories across all 12 months (sum the deposit-withdrawal for each category (just so withdrawals are negative).

For example, Jan15 might look like

Check        Date                        Desc                                                Category                   Withdraw                Deposit               Balance
114           01-Jan-15    French fries for concessions                Concessions                    75.00                                               2,500.00
115           05-Jan-15            Cleaning Supplies                           Field House                     10.00                                               2,490.00
                  06-Jan-15        Pizza Fundraiser                                 Fundraiser                                                         200                2,690.00

What we are trying to get is a tab that sums ALL 'concessions' category line items, all 'field house' category, etc.
Chuck BrownAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Why not have all the data in one Table? The summarise with a PiviotTable with dates grouped by months.
PivotSakes.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I have an easy VBA solution for you to achieve this.
Assuming you have one Sheet named Summary in your workbook along with 12 month's sheets.
Then Right Click on Summary Tab --> View Code --> Paste the code given below in the opened code window --> Save your workbook as Macro-Enabled Workbook.

How will the code work?

The Summary Sheet Module will have a code for Sheet Activate Event i.e. whenever you change a value in months sheet and come back to your Summary Sheet, it will be automatically updated with the latest values from all the months sheets. The Summary Sheet contains no formulas, so if you delete all the values from the Summary Sheet (Don't delete the column and row headers) and select another sheet and select the Summary Sheet again, all the values will be appeared again.

Code is as follows......
Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim lr As Long
Dim rng As Range, cell As Range
Dim Deposit As Double, Withdrawal As Double
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & lr)
Application.ScreenUpdating = False
For Each cell In rng
        For Each ws In Worksheets
            If ws.Name <> "Summary" Then
                Deposit = Deposit + WorksheetFunction.SumIf(ws.Columns(4), cell, ws.Columns(5))
                Withdrawal = Withdrawal + WorksheetFunction.SumIf(ws.Columns(4), cell, ws.Columns(6))
            End If
        Next ws
    cell.Offset(0, 1) = Deposit
    cell.Offset(0, 2) = Withdrawal
    cell.Offset(0, 3) = Deposit - Withdrawal
    Deposit = 0
    Withdrawal = 0
Next cell
Application.ScreenUpdating = True
MsgBox "Summary Sheet has been updated successfully.", vbInformation, "Summary Updated!"
End Sub

Open in new window

Please find the attached workbook to see how will this work. Right now I have deleted all the calculations from the Summary Sheet just to demonstrate you that how does this work. So select any month tab and come back to Summary Sheet, you will find all the calculations in place.

I have populated the months sheet with some dummy data.
Don't forget to enable macros after downloading the file while prompted.
Let me know if this is something you can work with.
Annual-Summary.xlsm
0
Chuck BrownAuthor Commented:
Roy_Cox - Best Solution
sktneer - Answers the question as asked
0
Roy CoxGroup Finance ManagerCommented:
Glad it helped. In my opinion all such data should always be on one sheet then use Excel's inbuilt features like PivotTables to summarise the data.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.