CODEWITHSUNDEEP
excelexcel-indirect
Steven
Steven

Reputation: 401

Excel INDIRECT returns with REF error

I don't understand why my INDIRECT function does not work. I'm dynamically changing D2 value from a list.

=INDIRECT("'G:\Production\OPH 2015\"&D2&"'!"&"G4")

where

D2 = [Spreadsheet.xslx]Sheet1

G4 would be the value I want the formula to return with.

The same formula on its own returns the value 72.2% and looks like this:

='G:\Production\OPH 2015\[Spreadsheet.xlsx]Sheet1'!G4

Upvotes: 1

Views: 3776

Answers (2)

SierraOscar
SierraOscar

Reputation: 17647

Q: "I don't understand why my INDIRECT function does not work"

A: From the MS Office Support Article for INDIRECT():

  • If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Upvotes: 1

mike7mike
mike7mike

Reputation: 433

It isn't your formula that is incorrect, the INDIRECT formula will return #REF! if the workbook is closed.

I haven't tried this, but I believe this add-in will solve your problem: http://www.ashishmathur.com/tag/indirect-ext/

Upvotes: 1

Related Questions