Building a Filename

[Egon]

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.

 

[Bob Phillips]

I haven't tested it but try this

=IF(A10<TODAY(),SUM(IF(INDIRECT("'S:\Major Projects\6917-11Gomez\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)

 

[Egon]

That doesn't seem to work either. It gives me an invalid cell reference
value I think, or something to the affect. I'm sure it has to do with
Excel looking at the data from the concatenate funcation as text and
not as a reference to another workbook.