oldest incomplete job
 

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)))

oldest incomplete job
 

=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)))

oldest incomplete job
 

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.

"floridasurfn" wrote:

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)))

oldest incomplete job
 

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



"floridasurfn" wrote:

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)))