Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities (i.e. John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the second
variant so that I don't have to pass the constant references everytime)....

Is this possible?

Well, you could do this,

Function GetPerson(rng1 As Range, name1, rng2 As Range, name2, rng3 As
Range)

GetPerson = Application.Caller.Parent.Evaluate( _
"SUMPRODUCT((" & rng1.Address & "=""" & name1 & """)*" & _
"(" & rng2.Address & "=""" & name2 & """)*" & _
"(" & rng3.Address & "))")

End Function

but

getPerson(D1:D10, "June", E1:E10, "Mark",F1:F10)

hardly saves much IMO

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"kMan" wrote in message
...
Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities (i.e.
John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the second
variant so that I don't have to pass the constant references
everytime)....

Is this possible?

Function getperson(s1 As String, s2 As String) As Integer
dq = Chr(34)
p1 = "=sumproduct((D1:D10=" & dq
p2 = dq & ")*(E1:E10=" & dq
p3 = dq & ")*(F1:F10))"
func = p1 & s1 & p2 & s2 & p3
getperson = Evaluate(func)
End Function


Use as:

=getperson("June","Mark")
--
Gary''s Student - gsnu200740


"kMan" wrote:

Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities (i.e. John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the second
variant so that I don't have to pass the constant references everytime)....

Is this possible?

I was thinking along the same lien, except my code doesn't work :( I think it
faults at the first line where I am attempting to set the range (a range in a
different worksheet).

Can you please help me? Thanks a mil.

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range


Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" &
officeNo & ")*(" & rngeState & "=" & state _
& ")*(" & rngeSkill & "=" & skillLevel & ")*" &
empRange & ")")
End Function


The aim of the function is to return sum in the empRange, given matches in
the other three columns... The three columns stay fixed, empRange moves
around a bit.....

Cheers

"Gary''s Student" wrote:

Function getperson(s1 As String, s2 As String) As Integer
dq = Chr(34)
p1 = "=sumproduct((D1:D10=" & dq
p2 = dq & ")*(E1:E10=" & dq
p3 = dq & ")*(F1:F10))"
func = p1 & s1 & p2 & s2 & p3
getperson = Evaluate(func)
End Function


Use as:

=getperson("June","Mark")
--
Gary''s Student - gsnu200740


"kMan" wrote:

Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities (i.e. John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the second
variant so that I don't have to pass the constant references everytime)....

Is this possible?

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range

Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "="
& officeNo & ")*" & _
"(" & rngeState.Address & "=" &
state & ")*" & _
"(" & rngeSkill.Address & "="""
& skillLevel & """)*" & _
empRange.Address & ")")
End Function


But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200,
or D1:D200 change, then the UDF does not recalculate.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kMan" wrote in message
...
I was thinking along the same lien, except my code doesn't work :( I think
it
faults at the first line where I am attempting to set the range (a range
in a
different worksheet).

Can you please help me? Thanks a mil.

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range


Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" &
officeNo & ")*(" & rngeState & "=" & state _
& ")*(" & rngeSkill & "=" & skillLevel & ")*" &
empRange & ")")
End Function


The aim of the function is to return sum in the empRange, given matches in
the other three columns... The three columns stay fixed, empRange moves
around a bit.....

Cheers

"Gary''s Student" wrote:

Function getperson(s1 As String, s2 As String) As Integer
dq = Chr(34)
p1 = "=sumproduct((D1:D10=" & dq
p2 = dq & ")*(E1:E10=" & dq
p3 = dq & ")*(F1:F10))"
func = p1 & s1 & p2 & s2 & p3
getperson = Evaluate(func)
End Function


Use as:

=getperson("June","Mark")
--
Gary''s Student - gsnu200740


"kMan" wrote:

Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities (i.e.
John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the second
variant so that I don't have to pass the constant references
everytime)....

Is this possible?

Thanks for your reply...

The function seems to breakdown on the first Set line (Set rngeOffice....).
If I put a breakpoint on the secondline, doesn't get to it and I get a "A
value used in the formula is a wrong data type" error in Excel....

I did consider the downside of declaring some of the ranges inside the
function... They remain fairly static and this is a one-off exercise, so
decided to make it easier on the eyes instead

"Bob Phillips" wrote:

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range

Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "="
& officeNo & ")*" & _
"(" & rngeState.Address & "=" &
state & ")*" & _
"(" & rngeSkill.Address & "="""
& skillLevel & """)*" & _
empRange.Address & ")")
End Function


But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200,
or D1:D200 change, then the UDF does not recalculate.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kMan" wrote in message
...
I was thinking along the same lien, except my code doesn't work :( I think
it
faults at the first line where I am attempting to set the range (a range
in a
different worksheet).

Can you please help me? Thanks a mil.

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range


Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" &
officeNo & ")*(" & rngeState & "=" & state _
& ")*(" & rngeSkill & "=" & skillLevel & ")*" &
empRange & ")")
End Function


The aim of the function is to return sum in the empRange, given matches in
the other three columns... The three columns stay fixed, empRange moves
around a bit.....

Cheers

"Gary''s Student" wrote:

Function getperson(s1 As String, s2 As String) As Integer
dq = Chr(34)
p1 = "=sumproduct((D1:D10=" & dq
p2 = dq & ")*(E1:E10=" & dq
p3 = dq & ")*(F1:F10))"
func = p1 & s1 & p2 & s2 & p3
getperson = Evaluate(func)
End Function


Use as:

=getperson("June","Mark")
--
Gary''s Student - gsnu200740


"kMan" wrote:

Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities (i.e.
John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the second
variant so that I don't have to pass the constant references
everytime)....

Is this possible?

That suggests that the value you are passing as officeNo or state is not a
number, or sillLevel is not a string.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kMan" wrote in message
...
Thanks for your reply...

The function seems to breakdown on the first Set line (Set
rngeOffice....).
If I put a breakpoint on the secondline, doesn't get to it and I get a "A
value used in the formula is a wrong data type" error in Excel....

I did consider the downside of declaring some of the ranges inside the
function... They remain fairly static and this is a one-off exercise, so
decided to make it easier on the eyes instead

"Bob Phillips" wrote:

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range

Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address &
"="
& officeNo & ")*" & _
"(" & rngeState.Address &
"=" &
state & ")*" & _
"(" & rngeSkill.Address &
"="""
& skillLevel & """)*" & _
empRange.Address & ")")
End Function


But this is poor UDF design, because if any of the ranges P1:P200,
Q1:Q200,
or D1:D200 change, then the UDF does not recalculate.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"kMan" wrote in message
...
I was thinking along the same lien, except my code doesn't work :( I
think
it
faults at the first line where I am attempting to set the range (a
range
in a
different worksheet).

Can you please help me? Thanks a mil.

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range


Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" &
officeNo & ")*(" & rngeState & "=" & state _
& ")*(" & rngeSkill & "=" & skillLevel &
")*" &
empRange & ")")
End Function


The aim of the function is to return sum in the empRange, given matches
in
the other three columns... The three columns stay fixed, empRange moves
around a bit.....

Cheers

"Gary''s Student" wrote:

Function getperson(s1 As String, s2 As String) As Integer
dq = Chr(34)
p1 = "=sumproduct((D1:D10=" & dq
p2 = dq & ")*(E1:E10=" & dq
p3 = dq & ")*(F1:F10))"
func = p1 & s1 & p2 & s2 & p3
getperson = Evaluate(func)
End Function


Use as:

=getperson("June","Mark")
--
Gary''s Student - gsnu200740


"kMan" wrote:

Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly
utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities
(i.e.
John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the
second
variant so that I don't have to pass the constant references
everytime)....

Is this possible?

Just some added:

Might as well just use strings to refer to the ranges. Bob's suggestion
failed to use the optiona arguments for Address, and so they would refer to
the activesheet and not the Staffing All Sites sheet (unless it was the
activesheet).

Also, just to highlight Bob's addition which you may have missed: since
SkillLevel is a string, it needs to be in double quotes in the formula:

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim r1 as String, r2 as String, r3 as String


r1 = "'Staffing All Sites'!$P$1:$P$200"
r2 = "'Staffing All Sites'!$Q$1:$Q$200"
r3 = "'Staffing All Sites'!$D$1:$D$200"

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & r1 & "=" _
& officeNo & ")*(" & r2 & "=" & state _
& ")*(" & r3 & "=""" & skillLevel & """)*" _
& empRange.Address(1,1,xlA1,True) & ")")
End Function

Here is an example of evaluating the string in the immediate window

r1 = "'Staffing All Sites'!P1:P200"
r2 = "'Staffing All Sites'!Q1:Q200"
r3 = "'Staffing All Sites'!D1:D200"
set empRange = Activesheet.Range("M1:M200")
officeNo = 5
State = 6
SkillLevel = "A"
? "SUMPRODUCT((" & r1 & "=" _
& officeNo & ")*(" & r2 & "=" & state _
& ")*(" & r3 & "=""" & skillLevel & """)*" _
& empRange.Address(1,1,xlA1,True) & ")"

'Produces:

SUMPRODUCT(('Staffing All Sites'!P1:P200=5)*('Staffing All
Sites'!Q1:Q200=6)*('Staffing All
Sites'!D1:D200="A")*[Book1]Sheet1!$M$1:$M$200)

--
Regards,
Tom Ogilvy


"kMan" wrote:

Thanks for your reply...

The function seems to breakdown on the first Set line (Set rngeOffice....).
If I put a breakpoint on the secondline, doesn't get to it and I get a "A
value used in the formula is a wrong data type" error in Excel....

I did consider the downside of declaring some of the ranges inside the
function... They remain fairly static and this is a one-off exercise, so
decided to make it easier on the eyes instead

"Bob Phillips" wrote:

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range

Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice.Address & "="
& officeNo & ")*" & _
"(" & rngeState.Address & "=" &
state & ")*" & _
"(" & rngeSkill.Address & "="""
& skillLevel & """)*" & _
empRange.Address & ")")
End Function


But this is poor UDF design, because if any of the ranges P1:P200, Q1:Q200,
or D1:D200 change, then the UDF does not recalculate.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kMan" wrote in message
...
I was thinking along the same lien, except my code doesn't work :( I think
it
faults at the first line where I am attempting to set the range (a range
in a
different worksheet).

Can you please help me? Thanks a mil.

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range


Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" &
officeNo & ")*(" & rngeState & "=" & state _
& ")*(" & rngeSkill & "=" & skillLevel & ")*" &
empRange & ")")
End Function


The aim of the function is to return sum in the empRange, given matches in
the other three columns... The three columns stay fixed, empRange moves
around a bit.....

Cheers

"Gary''s Student" wrote:

Function getperson(s1 As String, s2 As String) As Integer
dq = Chr(34)
p1 = "=sumproduct((D1:D10=" & dq
p2 = dq & ")*(E1:E10=" & dq
p3 = dq & ")*(F1:F10))"
func = p1 & s1 & p2 & s2 & p3
getperson = Evaluate(func)
End Function


Use as:

=getperson("June","Mark")
--
Gary''s Student - gsnu200740


"kMan" wrote:

Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities (i.e.
John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the second
variant so that I don't have to pass the constant references
everytime)....

Is this possible?