Link to home
Start Free TrialLog in
Avatar of Scamquist
ScamquistFlag for United States of America

asked on

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


This should return the equilivent


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 2007
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

There are two reference functions that do not work on a closed workbook: INDIRECT (returns #REF!) and OFFSET (returns #VALUE!) There is no alternative to the INDIRECT function without VBA or third party add-in. Instead of the OFFSET function use the INDEX function. The difference between INDEX from OFFSET is that the INDEX function requires a range of cells and will only return one cell at a time. Also be aware that the INDEX function offsets from 0 where the OFFSET function offsets from 1:


is equivalent to


Two commonly used aggregation functions, COUNTIF and SUMIF, also do not work on a closed workbook. These are easily emulated with SUMPRODUCT as illustrated below.

The COUNTIF function


is equivalent to


The SUMIF function


is equivalent to


Correction, above I stated that INDIRECT returns one cell at a time. INDIRECT can return a row or column from the source range if one or the other is omitted. For example, to sum the range D1:D10 from the range A1:E10 with INDIRECT:


Avatar of byundt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scamquist


Thank you very much.  This works quote well.