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

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 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:

   =OFFSET(A1,2,0)

is equivalent to

   =INDEX(A:A,3,1)

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

   =COUNTIF(A1:A100,10)

is equivalent to

   =SUMPRODUCT((A1:A100=10)*1)

The SUMIF function

   =SUMIF(A1:A100,"=Yes",B1:B100)

is equivalent to

   =SUMPRODUCT((A1:A100="Yes")*B1:B100)

Kevin
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:

   =SUM(INDIRECT(A1:E10,,4))

Kevin
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
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

ASKER

Thank you very much.  This works quote well.