Concatenating date field into text ands using in a formula

[Jason Prebble]

Hi all,

Hope you can help.

I have a date field (B1) and am trying to concatenate this into a
directory path as part of a formula to pull data from another
worksheet.
IE My worksheets are named open"date of report".xls
B1 contains "date of report"
I have tried concatenating the worksheet name in a field and then
using that field.
I have tried cocatenating the directory path in a field and using that
field.
My last attempt at a formula is

=SUM(IF('=concatenate("D:\CSA\Reporting\Open
Jobs\[open"&TEXT(B1,"ddmmyy")&".xls]Customer")'!$I$3:$I$96=$A$41,IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$C$3:$C$96<B48,1,0),0))

Date is 110903, worksheet name is open110903.xls.

If I manually type it in as below the formaula works dream.

=SUM(IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$I$3:$I$96=$A$41,IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$C$3:$C$96<B48,1,0),0))

Any assistance or direction pointing would be greatly appreciated.
I hope I have provided enough info.

Cheers Jason