oldest incomplete job

[Guest]

i am trying to develop a function to tell me the oldest incomplete order for
a certain client.
i am using an array formula, with a reference to a page called "client" and
an indirect reference on a page called "calculations"

i came up with this formula but it returns a value of 1/0/1900
=MIN(IF(INDIRECT("CLIENT!$I$2:$I"&'Calculations
'!$O$1)="HOMESIDE",INDIRECT("CLIENT!$J$2:$J"&'Calculations'!$O$1)
="NO",INDIRECT("CLIENT!$D$2:$D"&'Calculations '!$O$1)))

*** the column "D" has entries that are not in date form. could this be the
problem?? the entries are asap & pending & tbd

the formula i based this off was an array for a max value:
=MAX(IF(INDIRECT("CLIENT!$I$2:$I"&'Calculations'!$O$1)="INTEGRATED"
,INDIRECT("CLIENT!$E$2:$E"&'Calculations '!$O$1)))

 

[Bob Phillips]

=MIN(IF((INDIRECT("CLIENT!$I$2:$I"&Calculations!$O$1)="HOMESIDE")*
(INDIRECT("CLIENT!$J$2:$J"&Calculations!$O$1)="NO"),
INDIRECT("CLIENT!$D$2:$D"&Calculations!$O$1)))

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)

 

[Guest]

Perhaps you could let us know what data is being compared. For example, the
oldest could be the maximum difference between TODAY and a set of dates for
client orders.

 

[Guest]

I would approach this with the Data > Filter > AutoFilter feature.....filter
first for the incomplete items, then for the largest date......this would
show you if there were more than one that met the condition also.

Vaya con Dios,
Chuck, CABGx3