Hello,

I have one worksheet, in which there are about 100 formulas based on another worksheets.

when i open it, all the cells with those formulas show a #ref error.

I have learnt that this is because none of those another worksheets are open. when i open one of those , the ref error goes away and my value is displayed correctly.

My problem is that, i cannot keep open all those worksheets.., but i need to see my values everytime i open my main worksheet without opening the other sheets..

I tried the indirect function but it doesnt quite help..

Please help

Thanks.

Hi seepra,

As far as I know, some special formulas in Excel might cause this problem. 

I tested your issue in my own environment, I used "Vlookup" and "Index" formulas to create a new workbook. Then I closed the source file and checked the result, they worked fine in my environment. Maybe my source data was very simple so I didn't get your issue.

I suggest you provide the sample of your files to our email address and I will help you check the files:
ibsofc@microsoft.com
 Note: Please add the URL of the case in the email subject or body.

I'm glad to help and follow up your reply.

Regards,

Emi Zhang
TechNet Community Su

Re:  external links showing ref error

Under File | Options | Advanced | When calculating...
   make sure that "Save external link values"  is check marked.

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
http://jmp.sh/K95N3ee