Use Cell Value In File Path

[mouse88]

I am using the following formula throughout 12 workbooks:

=INDEX('C:\Test\01.06.11\[SUMMARY.xlsm]CC'!$D:$D, MATCH(A68, 'C:\Test\01.06.11\[SUMMARY.xlsm]CC'!$C:$C, 0) + 0)

Is there any way I can get the formula to take the date '01.06.11' from a cell in the workbook.

Currently I have 12 workbooks and each has about 2000+ of these formulas. When I want to set them up for a new month I have to do a find & replace on them to change the month in the date and this takes far too much time.

Any advice appreciated.

Thanks

 

[RSXchin]

Sorry, that was wrong

This should be it

=INDEX("C:\Test\"&A1&'\[SUMMARY.xlsm]CC'!$D:$D, MATCH(A68, "C:\Test\"&A1&'\[SUMMARY.xlsm]CC'!$C:$C, 0) + 0)

 

[mouse88]

Have just tried and does not work.

I get a #REF error and where it has the workbook name in the formula it defaults that to show the c:\ drive before it.

Thanks

 

[mouse88]

Anyone?

 

[mouse88]

Still having trouble with this, can anyone help?

Matt