Formula Contains One or More Invalid References ??

[monirg]

Hello;

I've looked at similar threads here and elsewhere, but couldn't find a solution that successfully applies to the problem .

1) I have a simple w/b consisting of 2 w/s and 2 Line charts (each in its own sheet).
NO VBA and NO links are used.

2) The w/b works fine with all its functionality intact.
One exception:
...menu "Save" works fine
...menu "Close" works fine
...menu "Exit" produces the message:

A formula in this worksheet (named "Data") contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and cell reference.

3) There're NO formulas on this w/s "Data":
...Edit::Go To::Special::Formula
...returns: No cells were found.
and all range names are checked/correct.

Could someone please advise on how to identify this "formula with invalid references on this worksheet" ??

Regards.

 

[RoryA]

FYI, you can get the add-in here.

 

[Haseeb Avarakkan]

Try Open & Repair.

Open a blank file, press Ctrl+O, locate the file & at the bottom click on the drop down near the Open button, select Open & repair then click Repair.

Always keep a back up of your file.

 

[monirg]

Hi Rory;

Will look at the Name Manager add-in, and thanks for the link (reply # 31).

I've just re-run the Read-Only XL 2003 file I sent you (*-5.xls) on another computer running XL 2007.
The workbook worked absolutely perfect, with NO errors whatsoever, even on Exit.

I thought a serious error of the type you've detected #REF! in the named ranges would've been flagged by my XL 2003, and most certainly by XL 2007. Agree ??

Nevertheless, I'll look at the Name Manager add-in as per your suggestion.

Kind regards.

 

[RoryA]

It depends whether the names are in use, which they don't appear to be (as I said, I don't get your error in 2003).

 

[monirg]

Haseeb;

I did try earlier Excel Detect and Repair, as well as File Open and Repair. Didn't solve the problem!
Thanks for the suggestion.


Regards.

 

[monirg]

Hi Rory;

1) I've successfully installed the Name Manager add-in as per your suggestion (reply # 30).

2) Under:
... XL::Tools::Name Manager::Name Manager
the correct 6 range names are nicely listed, each as a global name in the w/b (as they should), and each with its correct formula.
Nothing is listed under "Local to any sheet", nothing under "Local to active sheet", and NO #REF! in any formula.

3) There's a chance (not sure how realistic!) that the simplified copy (*-5.xls) I sent you yesterday is corrupt for whatever reason, or one of us has a defective XL 2003 software!

4) I'll create another simplified copy of the original XL 2003 problem file and email it to you to look at (if you're still interested!).
In the meantime, here's my XL 2003 version:
Microsoft Office Excel 2003 (11.8328.8329) SP3
(and it is NOT OEM)
How does it compare with yours ??

Regards.

 

[RoryA]

Based on your answer #2, there is no way we are looking at the same workbook. Feel free to send me a copy again.
Note: I checked your latest copy on 2010 too - there were the same #REF! issues in the global names, but not in the local ones.

 

[monirg]

Rory;

I'm trying to figure out what's going on, so please bear with me!

When you open (in XL 2003) the copy I sent you yesterday (*-5.xls), do nothing, and then File::Close:
Does XL 2003 ask if you want to save the changes you made ??

Regards.

 

[RoryA]

Yes, it does.

 

[RoryA]

I have just sent you a corrected version of your file, with the erroneous names removed and the category labels on the rowchart corrected.