Programmatically create totals and names??

[Jack]

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

 

[Nigel]

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

 

[Jack]

Will pass this to our developer. Thanks.
Jack