Referencing a file when it is open/closed

[RSilverstein]

I have two workbooks: one with raw data, and one that uses that data.

My 1st workbook references cells within the 2nd one using:

=INDIRECT("'[Next Level Input.xls]Sheet1'!"&"$C"&$F$4)

Essentially, I am looking for it to return the data in a cell: with column "C" and the row of F4.


All of the references work fine when the file "Next Level Input" is open, but if that file is closed, I get a #REF! error.

Does anyone know why? And how to fix that. Right now I just have to open up "Next Level Input" whenever I want to work with my original workbook.

Thanks,

RSilverstein

 

[barry houdini]

The INDIRECT function doesn't work with closed workbooks, try using INDEX instead, i.e.

=INDEX('[Next Level Input.xls]Sheet1'!$C:$C,$F$4)

 

[xenou]

See Schielrn's post and link here:

http://www.mrexcel.com/forum/showthread.php?t=323175&highlight=indirect

Regards, AB

 

[RSilverstein]

Thanks a lot.

Quick question: why ever use INDIRECT instead of INDEX?

 

[barry houdini]

There are other situations where INDIRECT is useful, for instance you might want to hold a variable worksheet name in a cell and use INDIRECT to refer to that sheet.......