Scamquist

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

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("'"&A

This should return the equilivent

HLOOKUP(I39,'R:\COMPONENTS

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

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

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

Kevin

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thank you very much. This works quote well.

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

is equivalent to

=SUMPRODUCT((A1:A100="Yes"

Kevin