Max

[MightyLeeMoon]

I have a couple sheets in Excel that have information as follows:


Table 1

Req || Req Date
A01 || 01/11/06
A02 || 01/13/06
A03 || 01/17/06
A04 || 01/20/06

Table 2

Req || Placed
A01 || 01/12/06
A01 || 01/13/06
A02 || 01/14/06
A01 || 01/12/06



Table 1 is where we keep track of the number of associates ordered. W
have the Requisition number and the date required in columns A and
(there are more columns, but these are the columns relevant to m
current dilemma.

Table 2 is where associates are entered. Each associate has
Requisition number in Column A so that we can tell what order that Ne
Hire is for. We have the Date they are placed and start working i
Column B.

I need to find out the date each requisition is completed. The mos
recent date for a particular Requisition in Column A in Table 2 shoul
tell me this information.

For instance, if Req A01 only needs 3 people, then the complete dat
would be 01/13/06 since that is the most recent date associates wit
Requisition A01. (That would also mean the Agency was 2 days overdue i
completing Requisition A01 since the Required Date was 01/11/06.)

I have tried some variations of MAX and DMAX but can't seem to ge
anything to get me the most recent date associated with a particula
Req #. DMAX seems to get close, but as I add more Reqs to column A, i
starts to get confused and kicks back the most recent date overall. An
when I change the formula to reference just a particular cell (In thi
case, "A01" is located in cell A2 in Table 1.)

Does anyone have an idea on how I can get this done? Is there a way t
utilize an array here?

Thanks

 

[Bob Phillips]

=MAX(IF(Sheet2!$A$2:$A$200=A2,Sheet2!$B$2:$B$200))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MightyLeeMoon" <[email protected]>
wrote in message
news:[email protected]...