Hi Walter,

Am Sun, 23 Jun 2013 09:53:23 +0100 schrieb Walter Briscoe:


A B C D E F G H I
1 Job Section Need ExpNeed Key Group0 Group1 Group2 Group3
2 1263 5 4 4 4 4 4 4 4
3 1264 1 4 5 5 5 5 5 5
4 1264 2 4 5 5 5 5 5 5
5 1264 3 5 5 5 5 5 5 5
6 1264 4 5 5 5 5 5 5 5
7 1264 5 4 5 5 5 5 5 5
8 1363 1 3 3 3 3 3 3 3

I find myself liking named ranges and have added the following:
Name Refers to
Jobs =Sheet1!$A$1:$A$8
Keys =Sheet1!$C$2:$C$8

if you make your names dynamic you don't need to calculate LastRow in
your function.

H) Column H (Group2) is a copy down of the array formula:
=MAX(IF($A$2:$A$3000=A2,$D$2:$D$3000))

You can use it like this:
=MAX(IF(Jobs=A2,Keys))
So you enter it with CTRL+Shift+Enter it is an array formula.
E.g. for 1264 in A3 it will give for Jobs:
False, False, True, True, True, True, True, Falseand so it takes for
Keys only the values with the corresponding True. And Max will calculate
the maximum of this values.

For your function you don't have to declare Jobs because you have a
named range and can use it. LastRow also is not needed. so you can
shorten your function to:

Function NeedsMax() As Double
Const NeedsCol As Integer = 3 ' Needs are in this column
Dim Job As Range ' Caller's row's job cell
Dim FirstRow As Long ' Caller's row's job's first row
Dim JobCount As Integer ' Count of Jobs with caller's Job
Dim NeedsRange As Range ' Needs with caller's job number.

Set Job = Cells(Application.Caller.Row, Range("Jobs").Column)
FirstRow = WorksheetFunction.Match(Job, Range("Jobs"), 0)
JobCount = WorksheetFunction.CountIf(Range("Jobs"), Job)

Set NeedsRange = Range(Cells(FirstRow, NeedsCol), _
Cells(FirstRow + JobCount - 1, NeedsCol))

' Get Maximum in that range
NeedsMax = WorksheetFunction.Max(NeedsRange)
End Function


Application.caller.row is the row the calls the function from the
worksheet. That will fail in VBA to DEBUG.PRINT


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2