Solved

Sum up commissions from Mastercard and Visa Spreadsheets part 1

Posted on 2013-02-07
29
258 Views
Last Modified: 2013-03-28
part 1 because this is the Mastercard part of the solution requested.

In the Mastercard workbook I have sheet names preceded by 'HMF'. I need a function that will go thru all those sheets and sum the values in Col M (Commission Balance) for a determined date range that I select.

That sum then needs to be inserted into the attached spreadsheetHMF-Commision-Report.xls
0
Comment
Question by:JaseSt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 12
  • 2
29 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 38864338
It makes it more difficult when working across workbooks to do this sort of thing.

I would suggest you do the main work in the Mastercard workbook, then directly reference result cells to the summary.

Here is the method to use in the Mastercard workbook:

http://www.xl-central.com/sum-single-criteria-multiple-sheets.html

You can substitute SUMIFS for SUMIF if using XL2007 or later and have multiple criteria (i.e start and end date)
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38867090
Will attend asap
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38871879
Is this a new function or already done ?
gowflow
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JaseSt
ID: 38872040
this is a new function, but similar, or course, to work you've already done with summing totals from tabs
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38872344
what is the date ? the previous month like usual ?
gowflow
0
 

Author Comment

by:JaseSt
ID: 38872427
No. As mentioned above, I need to manually select a date range:

"I need a function that will go thru all those sheets and sum the values in Col M (Commission Balance) for a determined date range that I select."
0
 

Author Comment

by:JaseSt
ID: 38877964
now it's me waiting for you instead of you waiting for me :)

got anything with this yet, gowflow? Customer has need for me to come with the figures and I loath having to go through all these pages and manually adding them up.

Thanks for your help.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38880490
Have you considered my suggestion?
0
 

Author Comment

by:JaseSt
ID: 38887534
thanks NB_VC but the function needs to sum values from a specific column where the date = a range I select from a different column.
0
 

Author Comment

by:JaseSt
ID: 38888801
Gowflow you there? Vacation? Computer problems?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38894198
No here sorry for overlooking this will attend shortly.
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38894442
ok as this is affecting both Visa and MC can we assume that this file
HMF-Commision-Report.xls
will be located in the same directory as the CreateMonthlyTotals folder ? if yes then this will avoid putting new buttons to select location for that file. Also can we agree that the name will be the name of the file oyu posted in here so that it is hard coded in both workbooks ?

if you agree with above then this should make the task lighter and speedier to execute.
Also seems that you want to choose a date range not specifically a month so it could be from 2/3/2013 to 26/3/2013 etc ... right ? and each request will update a row in the file ?

Last but not least do you have a problem with the following
HMF Commissions      Date Range                                      Visa      Mastercard
                                    2/3/2013 to 26/3/2013                        1200
                                    2/3/2013 to 26/3/2013                                                     1500

or you want them like this
                                    2/3/2013 to 26/3/2013                         1200                     1500

gowflow
0
 

Author Comment

by:JaseSt
ID: 38895089
yes, to all your questions, however, would like the previous totals displayed, like::

12/2/2012 to 01/12/2013:
1/12/2013 to 2/4/2013:
2/4/2013 to 3/15/2013:
and yes, visa and mastercard can be on one line
0
 
LVL 31

Accepted Solution

by:
gowflow earned 500 total points
ID: 38899330
ok here it is:

1) Make a new copy of your latest MC file and give it a new name.
2) open it and goto VBA and doubleclick on module1 and after any End Sub paste the below code

Sub SumUpHMFComissions()
Dim NewWB As Workbook
Dim ThisWB As Workbook
Dim WS As Worksheet
Dim NewWS As Worksheet
Dim MaxRow As Long, I As Long, DateCol As Long
Dim HMFCommission As Double
Dim Todate As Date, SDate As Date
Dim RngE As Range
Dim cCell As Range

If gstFolderMonthlyTotalsFile = "" Then
    MsgBox ("You need to select a destination folder to store the Monthly Totals/HMF-Commission-Report File created. Please go to Sheet 'Main' and select a folder before proceeding further.")
    Exit Sub
Else
    If MsgBox("This process will Update file 'HMF-Commission-Report.xls' with the total comissions from Col M in all of HMF worksheets for the date range selected." & Chr(10) & Chr(10) _
        & "Are you ready to start this process ?", vbQuestion + vbYesNo, "HMF Commissions") = vbYes Then
        
        Application.ScreenUpdating = False
        Set ThisWB = ActiveWorkbook
        
        '---> Input Start Date
        Do
            SDate = InputBox("Please input Date Start", "HMF Commission Start Date", DateValue(Now))
        Loop Until IsDate(SDate)
        
        '---> Input To Date
        Do
            Todate = InputBox("Please input Date End", "HMF Commission End Date", DateValue(Now))
        Loop Until IsDate(Todate)
        
        
        '---> new procedure for opening the default blankMonthlyTotals set by gowflow on 3/2/2012
        On Error Resume Next
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
        Application.EnableEvents = False
        Set NewWB = Workbooks.Open(Filename:=gstFolderMonthlyTotalsFile & "HMF-Commision-Report.xls")

        Application.EnableEvents = True
        If err <> 0 Then
            MsgBox ("HMF-Commision-Report.xls file was not found please create it or adjust file folder location then re-start this procedure.")
            Exit Sub
        End If
        On Error GoTo 0
        
        Set NewWS = NewWB.ActiveSheet
        MaxRow = NewWS.UsedRange.Rows.count + 1
        MsgBox ("File HMF-Commision-Report.xls was found and will Update necessary info.")

        
        '---> Go thru all shteets in the current workbook except a list of them and
        '---> Summ Col L for all records where Date in Col K = the month before
        '---> Put that Value in Cell C17 of BlankMonthlyTotals file
        HMFCommission = 0
        '---> Col K
        DateCol = 11
        
        '---> Cumulate Sum of all Col M where Date range is repsected in Col K
        For Each WS In ThisWB.Worksheets
            
            If UCase(Left(WS.Name, 3)) = "HMF" Then
                '---> Filter Records in Col K where Date between SDate and ToDate
                WS.UsedRange.AutoFilter Field:=DateCol, Criteria1:=">=" & SDate, Operator:=xlAnd, Criteria2:="<=" & Todate
                
                '---> Affect to RngE the visible data
                '---> Summ All Values in Col L that meet last month date and affect to C17
                Set RngE = WS.Range("M:M").SpecialCells(xlCellTypeVisible)
                HMFCommission = HMFCommission + Application.WorksheetFunction.Sum(RngE)
                WS.ShowAllData
                WS.AutoFilterMode = False
            End If
        
        Next WS
        
        DateRange = Format(SDate & " to " & Todate)
        
        '---> find if Same occurence already there update the same row
        Set cCell = NewWS.UsedRange.Find(what:=DateRange, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        
        
        If Not cCell Is Nothing Then
            '---> Update Row Found with the Values
            NewWS.Cells(cCell.Row, "D") = HMFCommission
            NewWS.Cells(cCell.Row, "D").NumberFormat = "\$#,##0.00_);[Red]($#,##0.00)"
        Else
            '---> Update New Record with the Values
            NewWS.Cells(MaxRow, "B") = DateRange
            NewWS.Cells(MaxRow, "D") = HMFCommission
            NewWS.Cells(MaxRow, "D").NumberFormat = "\$#,##0.00_);[Red]($#,##0.00)"
        End If
    
        '---> Close Commission Workbook.
        NewWB.Save
        NewWB.Close
        Application.ScreenUpdating = True
        
        '---> Give Feedback
        MsgBox ("HMF-Commission-Report.xls have been updated with the commissions related to date range from " & DateRange & " successfully.")
    
    End If

End If

End Sub

Open in new window


3) SAVE the workbook.
4) Open sheet Main and in the bottom under the last commandbutton put a new button like the below picture


new button
You can choose the developper menu and press on design and click on the blue button and copy it and change its caption like in the picture.

double click on the new created button and put this
SumUpHMFComissions

5) SAVE and exit the workbook
6) Open it and give it a try.

gowflow
0
 

Author Comment

by:JaseSt
ID: 38899748
Thanks, gowflow. I ran it but the total is quite different that when I added it manually. I come up with $171.25 for the HMF Account page and then $581.99 for all the pages with HMF as the first letters of the name of the sheet.
0
 

Author Comment

by:JaseSt
ID: 38899753
I will go through it again and make sure I didn't miss something. I should have stated that your macro came up with $830.46 and I manually added $753.24
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38900512
make sure of the date range it takes into consideration both limits
from 1/1/2013 to 1/31/2013 includes both 1 and 31.

Also something else comes to mind as I often saw in the past you hv tendancy to put comments in the Amount field so here again if you have in Col M something  like
83.45 but this was a return ...
like amount and some text coments or whatever then for sure this is concidered as text and will not be included in the total ! Just though good to mention this.
gowflow
0
 

Author Closing Comment

by:JaseSt
ID: 38902370
Your sum is correct.  I just missed a value. Thank you very much for your help, gowflow. I'm going to skip doing the Visa part for now as it is very easy to sum up manually. I will ask for your help if that changes. Thanks again, gowflow. It is much appreciated.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38902381
Tks. any other help needed pls do not spare me.
Rgds/gowflow
0
 

Author Comment

by:JaseSt
ID: 38953486
hi gowflow! Ready for another one?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38953876
yep
gowflow
0
 

Author Comment

by:JaseSt
ID: 38984017
gowflow - I have one project too complex to do right now or explain - and am not sure how it can be done, but in the meantime I have another.

If you remember the functions for both the Visa and Mastercard workbooks that calculates the monthly totals for the previous month, I have an additional feat I would like that macro to do and please tell me if it can be done and if you can/want to do it.

It is this. I hope I can describe it understandably.

- have the function search two specific folders in my Outlook where the emails have the date = to last month. Then look for specific email addresses within that batch, total the number of those specific email address and put that total in specific cells of the Monthly Totals spreadsheet.

If you think this can be done, let me know and I will post a question and the link to it here.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38984067
you said:


I have one project too complex to do right now or explain

Is this what you tried to exaplain later ... the lookup in outlook for 2 folders etc ... or it is something else ?

For the lookup in outlook for 2 folders yes I can assist.

If you previous answer is NO and it is a total diffrent other project then I am willing to go private and assist you basis a reasonable fee. If your intrested let me know.

gowflow
0
 

Author Comment

by:JaseSt
ID: 38985718
It is the lookup in two folders. I will explain in a question I will post in a day or two.

Thanks for your offer to help, yet once again.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39024112
When I looked at it then you were already well served. Let me know if u need other help.
gowflow
0
 

Author Comment

by:JaseSt
ID: 39024757
yes, I do, gowflow - thank you - but it is a problem with a previous solution and I will post a link to it here
0
 

Author Comment

by:JaseSt
ID: 39025365
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28078554.html

Here it is gowflow. Remember when I mentioned that I wanted the MCR entries to NOT create a space? Well, it has caused another problem that I'm just getting around to telling you. Thanks for your efforts. Be nice to get this one fixed.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39027904
Is this MC or Visa ?
gowflow
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

623 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