Indirect - vs - Index

[pdt060776]

I am using the following formula to pull data out of a External Spreadsheet, and works perfectly if I have the CH EXTRUSION SHIFT REPORT.xls file open, but as I am using Indirect function this will not work if the workbook is closed. Can I use the Index function or something else to make this work ?

Please can you give me an example ?

=VLOOKUP(IF(ISNUMBER($A8),LEFT(TEXT($A8,"0"),7),$A8),INDIRECT("'[CH EXTRUSION SHIFT REPORT.xls]" & DAY($B$3)+D$2 & "-"&D$4 & "'!$D:$N"),9,FALSE)