Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS group, the
answer 11/27/04,,, Can someone help me?

Tks in advance..

Hi Jim,

=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13))

as an aray formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:dJQge.4203$It1.55@lakeread02...
Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS group, the
answer 11/27/04,,, Can someone help me?

Tks in advance..

Hi!

Try this:

Array entered:

=MIN(IF(Sheet1!A$1:A$13=D4,Sheet1!C$:C$13))

Format as DATE

Biff

"Jim May" wrote in message
news:dJQge.4203$It1.55@lakeread02...
Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS group, the
answer 11/27/04,,, Can someone help me?

Tks in advance..

much appreciated Bob;
works great!!
Jim

"Bob Phillips" wrote in message
...
Hi Jim,

=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13))

as an aray formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:dJQge.4203$It1.55@lakeread02...
Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS group,
the
answer 11/27/04,,, Can someone help me?

Tks in advance..

Thanks Biff for the help!
Jim

"Biff" wrote in message
...
Hi!

Try this:

Array entered:

=MIN(IF(Sheet1!A$1:A$13=D4,Sheet1!C$:C$13))

Format as DATE

Biff

"Jim May" wrote in message
news:dJQge.4203$It1.55@lakeread02...
Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS group,
the
answer 11/27/04,,, Can someone help me?

Tks in advance..

Bob:
The CSE formula worked perfect in my (Small and simple) example;
but when I applied it against a huge set of data (1500 rows --for a fellow
employee)
it produced 01/00/00 (Date formatted) Value of 0.
This probably due to perhaps some "foreign-crap" within the Ranges
Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc.

Can I test (On my Sheet1) using a temporary helper column to determine the
culprit cell(s) causing my final CSE formula to produce the 0 value?
Thanks for your help!!
Jim

"Bob Phillips" wrote in message
...
Hi Jim,

=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13))

as an aray formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:dJQge.4203$It1.55@lakeread02...
Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS group,
the
answer 11/27/04,,, Can someone help me?

Tks in advance..

Hi Jim,

Why not just filter the date column for a 0 date?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:4rmhe.5436$It1.3577@lakeread02...
Bob:
The CSE formula worked perfect in my (Small and simple) example;
but when I applied it against a huge set of data (1500 rows --for a fellow
employee)
it produced 01/00/00 (Date formatted) Value of 0.
This probably due to perhaps some "foreign-crap" within the Ranges
Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc.

Can I test (On my Sheet1) using a temporary helper column to determine the
culprit cell(s) causing my final CSE formula to produce the 0 value?
Thanks for your help!!
Jim

"Bob Phillips" wrote in message
...
Hi Jim,

=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13))

as an aray formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:dJQge.4203$It1.55@lakeread02...
Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS group,
the
answer 11/27/04,,, Can someone help me?

Tks in advance..

In my sample range A1:A13 there was a single blank cell of Text
which was causing the problem - once I filled it in the original formula
worked.

I tried (to eliminate this possibility from happening again by trying the
following but it didn't seem to help/work...
=MIN(IF(AND(miss!$D$5:$D$1444<"",miss!$D$5:$D$144 4=$B$4),miss!$E$5:$E$1444)
) <<< where D:D column hold my VCode (one SMPC-Text)
and E:E holds the dates..

Tks Bob,
Jim May

"Bob Phillips" wrote in message
...
Hi Jim,

Why not just filter the date column for a 0 date?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:4rmhe.5436$It1.3577@lakeread02...
Bob:
The CSE formula worked perfect in my (Small and simple) example;
but when I applied it against a huge set of data (1500 rows --for a
fellow
employee)
it produced 01/00/00 (Date formatted) Value of 0.
This probably due to perhaps some "foreign-crap" within the Ranges
Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc.

Can I test (On my Sheet1) using a temporary helper column to determine
the
culprit cell(s) causing my final CSE formula to produce the 0 value?
Thanks for your help!!
Jim

"Bob Phillips" wrote in message
...
Hi Jim,

=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13))

as an aray formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:dJQge.4203$It1.55@lakeread02...
Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS
group,
the
answer 11/27/04,,, Can someone help me?

Tks in advance..

Jim,

Is this what you want?

=MIN(IF((miss!$D$5:$D$1444=$B$4)*(miss!$E$5:$E$144 4<""),miss!$E$5:$E$1444))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:2uqhe.5439$It1.2570@lakeread02...
In my sample range A1:A13 there was a single blank cell of Text
which was causing the problem - once I filled it in the original formula
worked.

I tried (to eliminate this possibility from happening again by trying the
following but it didn't seem to help/work...

=MIN(IF(AND(miss!$D$5:$D$1444<"",miss!$D$5:$D$144 4=$B$4),miss!$E$5:$E$1444)
) <<< where D:D column hold my VCode (one SMPC-Text)
and E:E holds the dates..

Tks Bob,
Jim May

"Bob Phillips" wrote in message
...
Hi Jim,

Why not just filter the date column for a 0 date?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:4rmhe.5436$It1.3577@lakeread02...
Bob:
The CSE formula worked perfect in my (Small and simple) example;
but when I applied it against a huge set of data (1500 rows --for a
fellow
employee)
it produced 01/00/00 (Date formatted) Value of 0.
This probably due to perhaps some "foreign-crap" within the Ranges
Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc.

Can I test (On my Sheet1) using a temporary helper column to determine
the
culprit cell(s) causing my final CSE formula to produce the 0 value?
Thanks for your help!!
Jim

"Bob Phillips" wrote in message
...
Hi Jim,

=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13))

as an aray formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:dJQge.4203$It1.55@lakeread02...
Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS
group,
the
answer 11/27/04,,, Can someone help me?

Tks in advance..