I have inherited a spreadsheet with a formula that does not work. Instead of
returning the earliest order date it returns the first order date. I would
like a formula to place in Sheet 2 that would populate the earliest order
date.

Sheet 1

Part Number Order Date
111 9/10/2007
113 1/1/2007
111 1/1/2007
113 2/1/2002

Sheet 2

Part Number Earliest Order Date
111 1/1/2007
113 2/1/2002

Thank you in advance!

Try this array* formula in B2 of Sheet2:

=MIN(IF((Sheet1!A2:A100=A2),Sheet1!B2:B100,10E10))

* Array formulae need to be committed using the key combiation Ctrl-
Shift-Enter (CSE) instead of the usual <enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yoruself. Use CSE
again if you subsequently edit or amend the formula.

Hope this helps.

Pete



On Jul 25, 7:51*pm, dcr wrote:
I have inherited a spreadsheet with a formula that does not work. Instead of
returning the earliest order date it returns the first order date. *I would
like a formula to place in Sheet 2 that would populate the earliest order
date.

Sheet 1

Part Number * Order Date
111 * * * * * * * * 9/10/2007
113 * * * * * * * * 1/1/2007
111 * * * * * * * * 1/1/2007
113 * * * * * * * * 2/1/2002

Sheet 2

Part Number * *Earliest Order Date
111 * * * * * * * * 1/1/2007
113 * * * * * * * * 2/1/2002

Thank you in advance!

=MIN(IF(Sheet1!$A$2:$A$200=$A2,Sheet1!$B$2:$B$200) )

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob

"dcr" wrote in message
...
I have inherited a spreadsheet with a formula that does not work. Instead
of
returning the earliest order date it returns the first order date. I
would
like a formula to place in Sheet 2 that would populate the earliest order
date.

Sheet 1

Part Number Order Date
111 9/10/2007
113 1/1/2007
111 1/1/2007
113 2/1/2002

Sheet 2

Part Number Earliest Order Date
111 1/1/2007
113 2/1/2002

Thank you in advance!

Bob,

Thank you for the reply. The result was the earliest order date for the
entire Sheet 1, not just for part number 111. Any suggestions? Is a VLOOKUP
required?

"Bob Phillips" wrote:

=MIN(IF(Sheet1!$A$2:$A$200=$A2,Sheet1!$B$2:$B$200) )

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob

"dcr" wrote in message
...
I have inherited a spreadsheet with a formula that does not work. Instead
of
returning the earliest order date it returns the first order date. I
would
like a formula to place in Sheet 2 that would populate the earliest order
date.

Sheet 1

Part Number Order Date
111 9/10/2007
113 1/1/2007
111 1/1/2007
113 2/1/2002

Sheet 2

Part Number Earliest Order Date
111 1/1/2007
113 2/1/2002

Thank you in advance!

Did you read what Bob said?


"this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter"

--


Regards,


Peo Sjoblom

"dcr" wrote in message
...
Bob,

Thank you for the reply. The result was the earliest order date for the
entire Sheet 1, not just for part number 111. Any suggestions? Is a
VLOOKUP
required?

"Bob Phillips" wrote:

=MIN(IF(Sheet1!$A$2:$A$200=$A2,Sheet1!$B$2:$B$200) )

this is an array formula, so commit with Ctrl-Shift-Enter, not just
Enter.

--
__________________________________
HTH

Bob

"dcr" wrote in message
...
I have inherited a spreadsheet with a formula that does not work.
Instead
of
returning the earliest order date it returns the first order date. I
would
like a formula to place in Sheet 2 that would populate the earliest
order
date.

Sheet 1

Part Number Order Date
111 9/10/2007
113 1/1/2007
111 1/1/2007
113 2/1/2002

Sheet 2

Part Number Earliest Order Date
111 1/1/2007
113 2/1/2002

Thank you in advance!

I used Enter instead of CTRL+Shift+Enter. Now it is working!!

Thank you :)

"dcr" wrote:

Bob,

Thank you for the reply. The result was the earliest order date for the
entire Sheet 1, not just for part number 111. Any suggestions? Is a VLOOKUP
required?

"Bob Phillips" wrote:

=MIN(IF(Sheet1!$A$2:$A$200=$A2,Sheet1!$B$2:$B$200) )

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob

"dcr" wrote in message
...
I have inherited a spreadsheet with a formula that does not work. Instead
of
returning the earliest order date it returns the first order date. I
would
like a formula to place in Sheet 2 that would populate the earliest order
date.

Sheet 1

Part Number Order Date
111 9/10/2007
113 1/1/2007
111 1/1/2007
113 2/1/2002

Sheet 2

Part Number Earliest Order Date
111 1/1/2007
113 2/1/2002

Thank you in advance!

=MIN(IF(Sheet1!A2:A100=A2,Sheet1!B2:B100))

which is an array formula, so commit with Ctrl-Shift-enter not just Enter

--
__________________________________
HTH

Bob

"dcr" wrote in message
...
I have inherited a spreadsheet with a formula that does not work. Instead
of
returning the earliest order date it returns the first order date. I
would
like a formula to place in Sheet 2 that would populate the earliest order
date.

Sheet 1

Part Number Order Date
111 9/10/2007
113 1/1/2007
111 1/1/2007
113 2/1/2002

Sheet 2

Part Number Earliest Order Date
111 1/1/2007
113 2/1/2002

Thank you in advance!