Col,

Put this array formula in M2, and copy across

=IF(I2='Planner 1st QTr'!$O$2,I2,MIN(IF($I$2:$L$2='Planner 1st
QTr'!$O$2,$I$2:$L$2)))

but make sure you do it as a single cell array formula, not a multi-cell,
that is don't select M2:P2 and then input it, just select M2, input it,
array it, and copy across.

--

HTH

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


"Col" wrote in message
...
Hi all,



First, thanks to those who helped me out with my concatenation problem in
an
earlier post.



Now another problem if I may,



I have a series of dates in cells I2:L2 which move up in ascending order
through the year.



I've done an array formula as below;



{=IF(I2:L2='Planner 1st Qtr'!O2,I2:L2,"No")}



Which looks to see if a cell in the range I2:L2 is greater than the cell
O2
on the Planner 1st Qtr tab (date type) and is formula is copied across
cells
M2 to P2.



I then use the following formula to display the nearest date to the cell
O2
on the Planner tab as above (as of course all four cells may be greater);



=IF(M2<"No",M2,IF(N2<"No",N2,IF(O2<"No",O2,P2)) )



Is there any way I can merge both formulas into one? I've tried all
different ways but keep getting errors.



Thanks again for any help,



Regards,



Colin.




--
Remove the 'old' to reply to me.