Finding the closest future date from Today from a list of dates

Hi,

I am trying to find a function that work sucessfully to find the next date from a list of future set dates.

For example, i have a list of significant dates: 01/07/2008, 14/07/2008, 29/07/2008, 12/08/2008. Using todays date I want to retrieve the closest date to today but in the future. So today is 18/07/2008, so the next date in that list is 29/07/2008.

I do have a function that shows this.
=MIN(IF(Lookups!$D$2:$D$213>TODAY(),Lookups!$D$2:$D$213))

(D2:D213 is my list of significant date)

I have this as an array (ctrl, shift, enter)

It works fine until i save and reopen the workbook. Then, the function looses it's working as an 'array-ness' so returns the incorrect value.

I am using a workbook that is shared, if that makes any difference. Also, I know array functions are supposed to have { } once C,S,E are pressed but it doesn't seem to be the case (despite it working until re-opened)

Could someone help as to why this is happening or perhaps suggest an alternative function to give the same results?

Thanks.

May be
=SMALL(IF(($d$2:$d$213>TODAY()),$d$2:$d$213),1)
It is an array formula
Best wishes
Sreedhar

thanks,

Again, it works as with my original formula. However, i think the main problem here is that when i re-open the workbook the 'array' bit of the formula is lost. If i re-select the formula and press ctrl, shift, enter, it works again.

Is there anyway to keep this function when the workbook is re-opened?

PROBLEM SOLVED.

I think it was because I created the array when it was a shared workbook.

Making the workbook un-shared, creating the array function, then saving as a shared workbook again has seemed to solve the problem.

Thanks.