Is there a workaround with #REF error message?

G

Guest

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.
 
J

Jim Rech

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Indirect formula errors if file is closed 1
How could I split a spreadsheet containing 600 rows, so there's 100rows in each new workbook 6
Copy paste code for a #REF 2
Getting #ref error after row / cell is this a limit in excel 2003 tiasal2 2
Creating copy of sheet into new file results in #REF errors 1
#Ref! error using INDIRECT for file name 2
Getting #ref error after row / cell is this a limit in excel 2003 tia sal2 4
Indirect( ) function loosing values when spreadsheets are closed 2

Top