Is there a workaround with #REF error message?
 

I have a spreadsheet that uses extensively OFFSET(INDIRECT(cell),0,0,1,1))
where cell refers to a cell containing a XLS name because the filename and
path change monthly. When I open the main file all my cells containing the
above formula show "#REF" until the spreadsheets it's referring to are open.
I have a macro that automatically opens all the referring spreadsheets ~12
files. Shouldn't Excel take a snapshot of the data once I save the main file?
I want to be able to view the data as it is without opening the 12 files it's
referring to. Is it possible? Any other approach is welcome.

Thanks.

Is there a workaround with #REF error message?
 

You must open the workbook with Excel in Manual calc mode. So before
opening this workbook, check Tools, Options, Calculation.

--
Jim
"matelot" wrote in message
...
I have a spreadsheet that uses extensively OFFSET(INDIRECT(cell),0,0,1,1))
where cell refers to a cell containing a XLS name because the filename and
path change monthly. When I open the main file all my cells containing the
above formula show "#REF" until the spreadsheets it's referring to are
open.
I have a macro that automatically opens all the referring spreadsheets ~12
files. Shouldn't Excel take a snapshot of the data once I save the main
file?
I want to be able to view the data as it is without opening the 12 files
it's
referring to. Is it possible? Any other approach is welcome.

Thanks.

Is there a workaround with #REF error message?
 

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

matelot wrote:

I have a spreadsheet that uses extensively OFFSET(INDIRECT(cell),0,0,1,1))
where cell refers to a cell containing a XLS name because the filename and
path change monthly. When I open the main file all my cells containing the
above formula show "#REF" until the spreadsheets it's referring to are open.
I have a macro that automatically opens all the referring spreadsheets ~12
files. Shouldn't Excel take a snapshot of the data once I save the main file?
I want to be able to view the data as it is without opening the 12 files it's
referring to. Is it possible? Any other approach is welcome.

Thanks.

--

Dave Peterson