=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)
"floridasurfn" wrote in message
...
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"&'Calc ulations'!$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)))