Solved

Sum up commissions from Mastercard and Visa Spreadsheets part 1

Posted on 2013-02-07
29
247 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
  • 15
  • 12
  • 2
29 Comments
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
Will attend asap
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Is this a new function or already done ?
gowflow
0
 

Author Comment

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

Expert Comment

by:gowflow
Comment Utility
what is the date ? the previous month like usual ?
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
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
Comment Utility
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
Comment Utility
Have you considered my suggestion?
0
 

Author Comment

by:JaseSt
Comment Utility
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
Comment Utility
Gowflow you there? Vacation? Computer problems?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
No here sorry for overlooking this will attend shortly.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:JaseSt
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

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

Author Comment

by:JaseSt
Comment Utility
hi gowflow! Ready for another one?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yep
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
Is this MC or Visa ?
gowflow
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

15 Experts available now in Live!

Get 1:1 Help Now