INDIRECT function

Feb 25th, 2002, 07:47 AM

The INDIRECT function brings up a #REF! error if you try and reference a workbook that is closed, however it works fine if the workbook is open. Is there anyway around this problem?

Aladin Akyurek · Feb 25th, 2002, 07:51 AM

No. Not with INDIRECT.

Andrew XJ · Feb 25th, 2002, 08:05 AM

You can find from the Excel 97 help that the workbook referenced by INDIRECT muse be open. I don't know whether Excel 2000 has any improvement on this.

Mark W. · Feb 25th, 2002, 08:10 AM

Quote:

On 2002-02-25 07:05, Andrew XJ wrote:
You can find from the Excel 97 help that the workbook referenced by INDIRECT muse be open. I don't know whether Excel 2000 has any improvement on this.

No changes in XL2000. As always only absolute references to a closed worksheet will return a value.

Andrew XJ · Feb 25th, 2002, 08:37 AM

To solve this problem, i use Auto_Open() to open the workbooks referenced by INDIRECT and hide the workbooks. Use Auto_Close() to close them. And force Auto_Open() and Auto_Close() to run them. Is there any other more efficient way?

[ This Message was edited by: Andrew XJ on 2002-02-25 07:39 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 07:47 AM	#1
Guest Posts: n/a	The INDIRECT function brings up a #REF! error if you try and reference a workbook that is closed, however it works fine if the workbook is open. Is there anyway around this problem?

Feb 25th, 2002, 07:51 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,314	No. Not with INDIRECT.

Feb 25th, 2002, 08:05 AM	#3
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	You can find from the Excel 97 help that the workbook referenced by INDIRECT muse be open. I don't know whether Excel 2000 has any improvement on this.

Feb 25th, 2002, 08:37 AM	#5
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	To solve this problem, i use Auto_Open() to open the workbooks referenced by INDIRECT and hide the workbooks. Use Auto_Close() to close them. And force Auto_Open() and Auto_Close() to run them. Is there any other more efficient way? [ This Message was edited by: Andrew XJ on 2002-02-25 07:39 ]