Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create totals and names??
Hi,
This may be a bit lengthy for this group so if I get no replies I will understand; however, I like to be thorough. Intro === We are working on a complex process to extract data from our time and billing system to generate commission statements for salespeople. This process is quite far along and we have gotten to the point where we have been able to automatically create a spreadsheet that has individually named sheets for each salesperson that contains basically the following data: Full/ | Part/ Consultant | Part | Term | Margin | Commission Num1 F A 15 200 Num2 F T 15 300 Num3 P A 20 100 Num4 P T 30 150 Num5 F A 25 300 Consultant: Name of the consultant working. Full/Part: Full or part-time employee. F=1 headcount and P = .5 headcount Active/Term: A = active and should be counted in headcount and margin calcs. T = terminated and is not to be counted in headcount or margin calcs. Margin: the gross profit margin for this consultant Commission: the commission to be paid. Problem ====== We do not know VBA, hardly at all. What we now need to do for multiple sheets is to: 1) Programmatically create some totals and insert them at the bottom of each sheet 2) Programmatically create some named ranges for these totals. They will be linked from another sheet Totals to be created ============== Headcount: headcount is the total headcount for all ACTIVE consultants. In the above table the headcount is equal to 1 + .5 + 1 ( 2.5) Total Commission: the total commission is the sum of all the numbers in the Commission column regardless of whether the person is Active or Terminated. Average Margin: the average margin is the average of the margins for all of the ACTIVE consultants, whether part-time or fulltime. In the above table the Average Margin is equal to (10 + 20 +25)/3 = 20 So, after the macro/VBA runs the table would look like this: Full/ | Part/ Consultant | Part | Term | Margin | Commission Num1 F A 15 200 Num2 F T 15 300 Num3 P A 20 100 Num4 P T 30 150 Num5 F A 25 300 ======= ======== Headcount= 2.5 20 1,050 And there would be 3 Names created: Sheet1!headcount Sheet2!Average-Margin Sheet3!Total-Commission Is anyone willing to point me in the right direction...we could probably figure it out if someone would give us an approach to use. Thanks in advance, Jack |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create totals and names??
Part 1 solution entering all totals on each sheet..... this acts on ALL
worksheets - you may not want this, if you have summary sheets etc. How are you going to put the summary in? - I recommend you have a macro create the summary sheet and add them as each sheet is computed? Sub Totaliser() Dim wS As Integer, xlr As Long, xR As Long Dim wMargin As Double, wMarCount As Long, wHead As Double, wCom As Double '-------------------------------------------------- ' loop thru all worksheets in current workbook ' ------------------------------------------------- For wS = 1 To ActiveWorkbook.Worksheets.Count With Worksheets(wS) '----------------------------------------------------------- ' reset counters and totalisers '----------------------------------------------------------- wMargin = 0: wMarCount = 0: wHead = 0: wCom = 0 '---------------------------------------------- ' get last row on current sheet '-------------------------------- xlr = .Cells(Rows.Count, 1).End(xlUp).Row '------------------------------------------------- ' test if the totals not on sheet then add them '------------------------------------------------- If .Cells(xlr, 1) < "Headcount" Then '---------------------------------------------------- ' process current sheet from row 2(?) to last row '---------------------------------------------------- For xR = 2 To xlr '------------------ ' sum headcount '------------------ Select Case Trim(.Cells(xR, 2)) Case Is = "F" wHead = wHead + 1 Case Is = "P" wHead = wHead + 0.5 Case Else .Cells(xR, 6) = "Error - Headcount" End Select '------------------------------------- ' selectively sum margin and counts '------------------------------------- If Trim(.Cells(xR, 3)) = "A" Then wMargin = wMargin + .Cells(xR, 4) wMarCount = wMarCount + 1 End If '------------------- ' sum commision '------------------- wCom = wCom + .Cells(xR, 5) Next xR '------------------------- ' write totals to sheet '------------------------- .Cells(xlr + 2, 1) = "Headcount" .Cells(xlr + 2, 2) = Format(wHead, "###,0.0") If wMarCount 0 Then .Cells(xlr + 2, 4) = Format(wMargin / wMarCount, "###,0.0") Else .Cells(xlr + 2, 4) = 0 End If .Cells(xlr + 2, 5) = Format(wCom, "###,0") End If End With Next wS End Sub -- Cheers Nigel "Jack" wrote in message oups.com... Hi, This may be a bit lengthy for this group so if I get no replies I will understand; however, I like to be thorough. Intro === We are working on a complex process to extract data from our time and billing system to generate commission statements for salespeople. This process is quite far along and we have gotten to the point where we have been able to automatically create a spreadsheet that has individually named sheets for each salesperson that contains basically the following data: Full/ | Part/ Consultant | Part | Term | Margin | Commission Num1 F A 15 200 Num2 F T 15 300 Num3 P A 20 100 Num4 P T 30 150 Num5 F A 25 300 Consultant: Name of the consultant working. Full/Part: Full or part-time employee. F=1 headcount and P = .5 headcount Active/Term: A = active and should be counted in headcount and margin calcs. T = terminated and is not to be counted in headcount or margin calcs. Margin: the gross profit margin for this consultant Commission: the commission to be paid. Problem ====== We do not know VBA, hardly at all. What we now need to do for multiple sheets is to: 1) Programmatically create some totals and insert them at the bottom of each sheet 2) Programmatically create some named ranges for these totals. They will be linked from another sheet Totals to be created ============== Headcount: headcount is the total headcount for all ACTIVE consultants. In the above table the headcount is equal to 1 + .5 + 1 ( 2.5) Total Commission: the total commission is the sum of all the numbers in the Commission column regardless of whether the person is Active or Terminated. Average Margin: the average margin is the average of the margins for all of the ACTIVE consultants, whether part-time or fulltime. In the above table the Average Margin is equal to (10 + 20 +25)/3 = 20 So, after the macro/VBA runs the table would look like this: Full/ | Part/ Consultant | Part | Term | Margin | Commission Num1 F A 15 200 Num2 F T 15 300 Num3 P A 20 100 Num4 P T 30 150 Num5 F A 25 300 ======= ======== Headcount= 2.5 20 1,050 And there would be 3 Names created: Sheet1!headcount Sheet2!Average-Margin Sheet3!Total-Commission Is anyone willing to point me in the right direction...we could probably figure it out if someone would give us an approach to use. Thanks in advance, Jack |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create totals and names??
Will pass this to our developer. Thanks.
Jack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create totals for close-matching names? | Excel Worksheet Functions | |||
programmatically create a macro | Excel Programming | |||
How to refer to programmatically Dynamice Range names | Excel Programming | |||
Defining Names Programmatically | Excel Programming | |||
How to create picklists programmatically | Excel Programming |