Here is the formula:

{=IF(A10<TODAY(),SUM(IF(CONCATENATE("'S:\Major Projects\6917-11
Gomez\revenue and cost tracking\Vessel Reports\[PS-MR-RP-DMR
",TEXT(A10,"dd mmm
yy"),".xls]DMR!$N$182:$Q$254")="CDI",IF('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$J$182:$M$254="Assist. Super.",1,0),0)),0)}

What I'm trying to do is build the filename to a file based on the
date. I have to reformat the date so that it matches the format fo the
file. The problem seems to be that the formula isn't calculating the
Concatenate first or is putting it as pure text to evaluate to "CDI." I
need it to evaluate the concatenate and then check for the results
based on the output of the concatenated string.

I've tried different variations, however, this seems to be the one that
is closest to working, although it doesn't want to evaluate properly.

Any help would be appreciated.

Thanks.
J.