Help with a formula to pick the next date after TODAY() date

[jtalbour]

Need so help with a formula that will tell me the first date of a pending PO that will arrive after todays date

Looking at the data below the date in A3 is what i am after

Any array formula that will look at the data below and if the following conditions are met
column A has dates
column b has numbers
and column c says "open" then tell me what the date is of the next open PO

Solving this is way above my abilities and any help would be great

Book2
	A	B	C	D
1	DUE	AMOUNT	STATUS	COST
2	10/23/2006	50	OPEN	20.8724
3	10/22/2006	42	OPEN	21.0724
4	10/17/2006	42	OPEN	20.8724
5	10/14/2006	84	OPEN	20.8724
6	10/10/2006	42	CLOSED	20.8724
7	10/7/2006	42	CLOSED	20.6499
8	10/3/2006	84	CLOSED	20.6825
Sheet1

 

[PaddyD]

assuming dates greater than today occur above today's entry in the list:

=INDEX(A1:A8,MAX(IF(C1:C8="OPEN",IF(A1:A8>TODAY(),ROW(A1:A8)))))

...entered with control + shift + enter, not just enter.

 

[ravishankar]

cells(1,1) = " =today()"
For a = 10 to 1
if cells(a,2) > cells(1,1) then
cells(a,2).interior.indexcolor = 6
endif
next a

I assume the dates are in column B and in decreasing order.
press Alt F8 and paste the above code and run it.

Ravi

 

[Aladin Akyurek]

=INDEX($A$2:$A$8,MATCH(MIN(IF($C$2:$C$8="OPEN",IF($A$2:$A$8>TODAY(),$A$2:$A$8,""))),$A$2:$A$8,0))

which needs to be confirmed with control+shift+enter, not just with enter.