Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Sum up commissions from Mastercard and Visa Spreadsheets part 1

Posted on 2013-02-07
Medium Priority
260 Views
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
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

LVL 23

Expert Comment

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

ID: 38867090
Will attend asap
gowflow
0

LVL 31

Expert Comment

ID: 38871879
Is this a new function or already done ?
gowflow
0

Author Comment

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

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

Author Comment

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

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

ID: 38880490
Have you considered my suggestion?
0

Author Comment

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

ID: 38888801
Gowflow you there? Vacation? Computer problems?
0

LVL 31

Expert Comment

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

LVL 31

Expert Comment

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

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

gowflow earned 2000 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
``````

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

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

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

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

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

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

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

Author Comment

ID: 38953486
hi gowflow! Ready for another one?
0

LVL 31

Expert Comment

ID: 38953876
yep
gowflow
0

Author Comment

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

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

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

Author Comment

ID: 38997924
0

LVL 31

Expert Comment

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

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

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

ID: 39027904
Is this MC or Visa ?
gowflow
0

## Featured Post

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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â€¦
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month6 days, 5 hours left to enroll

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

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