INDIRECT function to a closed workbook

[SerenityNetwork]

I would like to use the INDIRECT function, but have the reference be a closed workbook. For example:
I have a worksheet "summary.xls" with the formula: {= SUM(IF(LEFT(DC-052.xls'!DC_RANGE,2)=LEFT($A3,2),1,0))} where DC_RANGE is a named range, and it works fine.
When I close the DC-052.xls then the formula in summary.xls becomes: {=SUM(IF(LEFT('C:\Documents and Settings\Me\Desktop\DC-052.xls'!DC_RANGE,2)=LEFT($A3,2),1,0))} and it still works fine.

I would like to replace the path and worksheet with an INDIRECT function. I intend to build the path, worksheet name, and named range from a formula.

However when I do this, it only works if both worksheets are open. When I close DC-052.xls then the formula in summary.xls gives me a #ref error. Even when I simply paste 'C:\Documents and Settings\Me\Desktop\DC-052.xls'!DC_RANGE into the cell the INDIRECT function references, I still get the error when the DC-052.xls workbook is closed.

I'm stumped. I'm guessing it might be the apostrophe at the beginning of the text string is causing problems, but I don't know for sure if this is it and/or how to get around the problem.

Any help will be greatly appreciated.

Thanks,
Andrew

[royUK]

INdirect requires the other workbook to be open I believe

[Domenic]

You can use INDIRECT.EXT if you download and install the free add-in Morefunc.xll...

[Bob Phillips]

You could also do A google search for Harlan Grove's PULL Function.

[royUK]

Found this

[Will Riley]

Originally Posted by SerenityNetwork

I would like to use the INDIRECT function, but have the reference be a closed workbook.

Roy's right. You can't use INDIRECT with closed workbooks.

Laurent Longre's Morefunc addin has a version that can be used on closed workbooks though, which you can download HERE

Edit: didn't see Domenic's reply