Excel 2010 - #REF messages that should NOT happen!

I have two workbooks located in the same folder.  
Workbook B contains formulas that refer to cells in a worksheet in workbook A.
I open both workbooks and workbook B is not giving me the results of those formulas.  Inspecting more closely, the formulas in B have a #REF in the formulas where the worksheet names should be.  The entire path is correct in the formula.  The workbook name was correctly indicated.  But the worksheet name was somehow and suddenly replaced with #REF even though the worksheet exists!  It is located in workbook A.
I close both workbooks and reopen them.  The error message goes away and all formulas are working.

Why is this happening and how can I prevent it from happening again?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
User INDIRECT when referring the cells which will prevent from this to happen. This occurs because of the changes you do in the destination cells the source sheet is pointing to.
So change your formula in workbook B to use INDIRECT() function to refer the cells in Workbook A.

The workbooks must be open otherwise it returns #REF

Solution is to use the full name like the following if the workbook is not open.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brothertruffle880Author Commented:
i don't understand how this happened.
I'm playing with the two files now and everything is fine.  I can't seem to reproduce the error.
Yes, I have the fully qualfied path in my references.  That's what mystified me.
What also mystified me was the fact that the error was so selective. It only gave me a #REF for the worksheet name, not the path, not the cell.
A #REF error will occur when a reference can't be found. In the environment you describe the most likely reason is the sequence in which you opened the workbooks. The referenced Wb must already be open when you open the referencing one. You may have done it the wrong way around once, not being aware. The cure you mention would work exactly as you describe in such a case.
I am unable to comment on why Excel decided to highlight only the sheet as missing without building a scenario different from the above. If the workbook was open and the sheet not accessible the presumption must be that the Wb was corrupt. Perhaps Excel managed to repair the damage when reloading the file.
Have you tried marahamn's suggestion to use INDIRECT addressing? This sounds like a promising approach. Please share your experience with it here. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.