• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

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?
2 Solutions
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.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now