HLOOKUP with INDIRECT returns #REF when target workbook closes.

I am trying to use indirect with hlookup.  I have am trying to revrieve info from a table on another drive.
The workbook I am using is on the C drive.
The target table array is in a folder on the R drive
I have a named range CV


cell A40 is the  DRIVE            the value in the cell is  R
Cell B40 is the  Folder       the value in the cell is  Components
Cell C40 is the  File Name    the value in the cell is  PVC.XLS
Cell D40 is the  Named Range  the value in the cell is  CV

I tried

HLOOKUP(I39,INDIRECT("'"&A40&":\"&B40&"\"&C40"'!"&D40),15,FALSE)


This should return the equilivent

HLOOKUP(I39,'R:\COMPONENTS\PVC.XLS'!CV,15,FALSE)

This works as long as the workbook PVC.XLS is open.  When I close it, I get #REF. If I open the target workbook back up, the proper value is displayed.

This is driving me nuts.

Any ideas?

Excel 2007Start Free Trial