Sum across Excel sheets - Data not uniform from sheet to sheet

[mkf001]

Subject Line: Sum across Excel sheets – Info not uniform from sheet to sheet
Hello,

Every month I track bank fees for each of our accounts. Each month has its individual sheet with detail on it. Along the left side of my Excel sheet I have various bank fees listed and across the top I have different account numbers. Each month I copy the previous month’s tab over, clear out the information and copy and paste information from a pivot table I create from an Excel file online. Some months we do not use some of the services, which is why the information is not always in the same cell. I've pasted an example of what my Excel sheets look like below.

What I would like to have is another sheet that will give me a year to date summary of how much money we spent on each bank fee for each account. In other words, I want to keep a running total of Bank Fee 1 for Account 1, a separate running total for Bank Fee 1 for Account 2 and so on. Is there a way to sum across all the sheets to do this if my information is not in the same cell from sheet to sheet?

My original thought was to write a formula that added together vlookup formulas in each month, however each month I would have to update my formula to include the new month. In addition, it also gives me the error #N/A since some of the charges are not on every month. I am hoping there is a solution that functions similar to the sum(first:last) function, but does not require that all the information be in the exact same cell on each sheet and will show an error if that charge is not listed for one of the months.

I have a somewhat intermediate knowledge level of Excel so I will need you to be specific in your instructions in order for me to understand. I am using Excel 2010 on a computer with Windows 7 Professional.

Thank you in advance for all your help.

Bank Fee Name Acct 1 Acct 2 Acct 3 Acct 4
Bank Fee 140 -150
Bank Fee 1 16 16
Bank Fee 3 5
Bank Fee 6 235.5
Bank Fee 7 145
Bank Fee 11
Bank Fee 15
Bank Fee 16 11 33
Bank Fee 145 55 55
Bank Fee 146 67.25 340
Bank Fee 19 60 60
Bank Fee 147 28.5
Bank Fee 148 5.75
Bank Fee 20 129 12.9 11.7 10.5
Bank Fee 21 5.5 181.5
Bank Fee 25

 

[Kuljack]

You stated you have an intermediate knowledge of excel, so your response to this question will likely be a no... but are you familiar with the use of Microsoft Access?

It seems this effort you hope to acheive would be easily obtained using Access because it captures relationships between data sources (i.e. tabs in your case), rather than scripting a cognitive formula to adapt with ongoing months.

Within access you would define tabs for each month, and in a summary tab Access would track the sum of all bank fees captured for the given account number, and also provides the third dimension to include/exclude given months.

However, if you're stuck in your way with excel or alternatively not ready for the complexity of Access, we could script a simple macro to draft the summary of each cells data into a "Recap" sheet if that would work for you.

 

[mkf001]

I don't have Microsoft Access on my computer and am not familiar with it so I am stuck with Excel. I'm not familiar with creating macros, but if it's "simple" then perhaps it's time I learned. Would you be willing to script a macro for me?

Thanks so much!

 

[Kuljack]

That is unfortunate but understandable.

I just need a few parameters layed out to start a script for you.

1. Will the rows (bank fees) be a set number, or will the number of bank fee names vary month month.
- I imagine some accounts will and will not have applicable amounts for the fees, I just mean the fees in general as place holders
2. Will the columns (account numbers) also be a set number or not
3. This will help to know how the macro should identify the ranges
4. Are you needing a total field anywhere for the accounts/bank fees
- for example the recap sheet will be a sum of all bank fee (1) for account #1234 but on the far right/bottom we could also capture the sum of all fees for #1234, or the sum of bank fee(1) for all accounts

Looking forward to your response!

 

[mkf001]

1) The rows are not a set number from month to month. As of now we have a total of 170 different bank fees, but usually only use about 70-90 of them each month.
- You are correct in assuming that not all accounts will have the bank fees each month.
2) The accounts are a set number for now. There are 31 accounts.
4) Ideally, I would like to sum both ways. It would greatly help with balancing.

Once the macro is set up I will be able to modify it to add bank fees and more account numbers, correct?


Let me know if you need further clarification on anything and I greatly appreciate your help!?

 

[Kuljack]

1) The rows are not a set number from month to month. As of now we have a total of 170 different bank fees, but usually only use about 70-90 of them each month.
- You are correct in assuming that not all accounts will have the bank fees each month.
2) The accounts are a set number for now. There are 31 accounts.
4) Ideally, I would like to sum both ways. It would greatly help with balancing.

Once the macro is set up I will be able to modify it to add bank fees and more account numbers, correct?


Let me know if you need further clarification on anything and I greatly appreciate your help!?

Perfect, I'll begin writing this out in a test file to make sure it works first. The recap sheet will lay out all 170 fees, and find match fees across the month sheets available. From there we can create a macro that identifies the fee's grand sum, if zero we can hide it as it's not applicable.

Will follow up once done.

 

[Kuljack]

Here is what I have so far, but currently I'm getting a 1004 error so I'm working on that. We may need to edit the path once I know the code is working to align with your spreadsheet, or maybe you can mold yours to the test form I've created.

Dim Fee As String
Dim FeeName As String
Dim Act As String
Dim FeeName2 As String
Dim Act2 As String

    For Each Row In Range(Cells(3, "A"), Cells(3, "A").End(xlDown))
        FeeName = Cells(Row.Row, 1)
        If FeeName = Empty Then
        Else
            For Each Column In Range(Cells(1, "C"), Cells(1, "C").End(xlRight))
                Fee = 0
                Act = Cells(1, Column.Column)
                If Act = Empty Then
                Else
                    Do Until ActiveSheet.Next Is Nothing
                        ActiveSheet.Next.Select
                        For Each Row2 In Range(Cells(2, "A"), Cells(2, "A").End(xlDown))
                            FeeName2 = Cells(Row2.Row, 1)
                            If FeeName2 = Empty Then
                            Else
                                If FeeName2 = FeeName Then
                                    For Each Column2 In Range(Cells(1, "B").End(xlRight))
                                        Act2 = Cells(1, Column2.Column)
                                        If Act2 = Empty Then
                                        Else
                                            If Act2 = Act Then
                                                Fee = Fee + Cells(Row2.Row, Column2.Column)
                                            Else
                                            End If
                                        End If
                                    Next
                                Else
                                End If
                            End If
                        Next
                    Loop
                    'Sum of fees for Fee/Account collected
                    Sheets("Recap").Select
                    Cells(Row.Row, Column.Column) = Fee
                End If
            Next
        End If
    Next

Essentiall Column A Looks something like this

Cell 1 Summary
Cell 2 [Blank]
Cell 3 Bank Fee 1
Cell 4 Bank Fee 2

...and so on

While Row 1 reads across this way

Column A Summary
Column B [Blank]
Column C Account Number 1
Column D Account Number 2

Where the Blank intersect between the two is the Grand Totals section, and the sum of values for each row/column are capture here. This was done to provide a clean up front value for totals, rather than them be on the outsides and have to move around more down road with remove/additions of fees or accounts.

 

[Kuljack]

Alright, so I've got it all worked out. I had to consult back to the forum with my code on the 1004 error but figured out the issue on my own. The command xlright was missing xlTOright so it was going nowhere and I had the formula anchored in Sheet1, rather than a module. Silly mistakes.

Here is your code!

Dim Fee As Integer
Dim FeeName As String
Dim Act As String
Dim FeeName2 As String
Dim Act2 As String

Application.EnableEvents = False
Application.ScreenUpdating = False
    For Each Row In Range(Cells(3, "A"), Cells(3, "A").End(xlDown))
        FeeName = Cells(Row.Row, 1)
        If FeeName = Empty Then
        Else
            For Each Column In Range(Cells(1, "C"), Cells(1, "C").End(xlToRight))
                Fee = 0
                Act = Cells(1, Column.Column)
                If Act = Empty Then
                Else
                    Do Until ActiveSheet.Next Is Nothing
                        ActiveSheet.Next.Select
                        For Each Row2 In Range(Cells(2, "A"), Cells(2, "A").End(xlDown))
                            FeeName2 = Cells(Row2.Row, 1)
                            If FeeName2 = Empty Then
                            Else
                                If FeeName2 = FeeName Then
                                    For Each Column2 In Range(Cells(1, "B"), Cells(1, "B").End(xlToRight))
                                        Act2 = Cells(1, Column2.Column)
                                        If Act2 = Empty Then
                                        Else
                                            If Act2 = Act Then
                                                Fee = Fee + Cells(Row2.Row, Column2.Column)
                                            Else
                                            End If
                                        End If
                                    Next
                                Else
                                End If
                            End If
                        Next
                    Loop
                    'Sum of fees for Fee/Account collected
                    Sheets("Recap").Select
                    Cells(Row.Row, Column.Column) = Fee
                    
                End If
            Next
        End If
    Next
Application.ScreenUpdating = True
Application.EnableEvents = True

Please read through it and let me know if you run into any issues. You'll either need to tweak the alignment to your workbook, or edit your workbook as mentioned before but I have tested this a few times and it runs smoothly in a module. Takes about 5-10 seconds to track through 170 potential bank fees for 31 accounts over two months... not bad.

 

[mkf001]

Wow! Thanks so much! I'll start working on this today and let you know if I have any questions.

 

[mkf001]

Okay so having a bit of trouble. I'm new to VBA and macros so I changed my sheet to fit what you described above and the macro runs, but it is currently just placing zeros in all the cells and does not show the grand totals. I'm not getting any error messages in Excel, but it's also not working as expected. Off the top of your head do you know what I may have done incorrectly? Or are there any suggestions I ought to try?

In addition, would it be possible to translate some of the code to English for me? I think eventually I will have to tweak the code to match my file, but first I need to understand the code.

I assume the first 5 lines are defining variables and line 8 and 12 is the beginning point for the data. I understand the logic behind the if then else statement and see that it will loop through until the end. However, beyond that I am confused about the code, particularly application.enablevents = False at the top and bottom of the code. FeeName= cells (Row.Row, 1) - what is defining?

I know I have a lot of questions and don't mean to be a pest, but I'd love to be able to understand the code and get this working and would appreciate any and all suggestions.

Thanks so much!