Search code examples
excelexcel-indirect

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
Solution

  • 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/