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.

 

[monirg]

Following is summary of Problem Description, Unsuccessful Attempts, and Accidental Solution ?
(XL 2003 SP3, Win XP SP3)

Problem Description:
1) A simple standalone w/b consists of 2 w/s with named dynamic ranges, and 2 Line charts (each in its own sheet).
NO VBA, NO Links, NO Forms, NO external data source, and NO w/s dynamic functions are used

2) The XL 2003 w/b works perfectly for ALL tested scenarios and with ALL its functionality intact.
...One exception:
...File::Save works fine
...File::Close works fine
...File::Exit produces the MS Excel error message:

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

Very strange! Only File::Exit produces the error message, everything else work fine!

3) The wordings of the MS Excel message: "A formula in this worksheet ...." appear to be somewhat misleading.
The Excel message is displayed regardless of whether a worksheet or a chart (which is not a w/s) is active, and whether the w/s contains formulas or not.

4) For example, the error message is displayed when there're NO formulas on this worksheet "Data":
...Edit::Go To::Special::Formula
...returns: No cells were found.
All named ranges throughout are checked/correct.

5) The XL 2003 problem file appears to be updating or calculating something when it is opened.
....Open/Close the file, and the save prompt: " .... do you want to save the changes you made ... " is displayed, despite the fact:
>> nothing has changed or updated (not to my knowledge); and
>> there's NO VBA, NO Links, NO Forms, NO external data source, and NO w/s dynamic functions

Unsuccessful Attempts:
6) Tried the following:
--> Used copy/move sheet by sheet and tested w/b after each. Same error message
--> Unchecked ALL the Add-Ins in the file, saved and closed the file
.....Re-opened the file. Made sure there was no Add-Ins checked on the list
.....File::Save and File::Close work fine, while File::Exit triggers the same irritating error message
--> Ran "XL 2003 Detect and Repair" and "File Open and Repair". No improvement
--> Removed all the charts and save it. Same problem
--> Deleting any 3 of the 4 sheets, didn't change the situation

7) Same file on a different computer (also running XL 2003) produced the same error "... invalid references ...".

8) The same XL 2003 problem file works fine and without a hitch when tested on a different computer running XL 2007, and Exits without a problem.
....That's to say, the MS Excel 2003 message DOES NOT display on File::Exit in XL 2007.

9) A read-only simplified copy of the problem file did not display the error message on Exit on Rory's XL 2003 computer, but showed according to Rory: "multiple versions of some named ranges together with #REF! in some global name definitions".
With the same read-only simplified file, none of these additional problems had been detected nor could be replicated on any of my computers running XL 2003.

10) Rory suggested: " ... there is no way we are looking at the same workbook."
.....I suggested: "one of us has a defective XL 2003 software."

Accidental Solution ?:
11) Installed Name Manager Add-In as per Rory's advice (ref. reply # 31), which made it easier to check all defined NAMES.
The correct range names were nicely listed, each as a global name in the w/b (as they should), and each with its correct formula.
Nothing was listed under "Local to any sheet", nothing under "Local to active sheet", and definitely NO #REF! found in any formula.
(By the way, the Name Manager Add-In is an excellent tool to manage defined names in workbooks)

12*) Since about yesterday (Nov 16, 2010), I've NOT been able to replicate the (irritating and somewhat strange) error message (Item 2 above) on any of my computers with the same read-only original problem file!!!!!
It's the same XL 2003 read-only file since the OP (Nov 13, 2010), and haven't changed the file or upgraded system hardware/software.

13) Out of curiosity, I checked the Excel Updates History on my computers.
.....Found Two (2) recent Updates for Microsoft Office 2003 Excel installed on Nov 15, 2010.

14) Whether the recent XL 2003 updates removed a bug, trapped/killed a virus, quietly fixed the (strange) problem, or something entirely different and the timing was just pure coincidence, is hard to tell!

The (strange) problem appears to be solved for now.
Thank you ALL for your contributions.
Special thanks to Rory (rorya, MrExcel MVP) for his tremendous help, time and patience.

 

[monirg]

For those who might be interested, I've just posted an interesting follow-up:

URGENT Advice Regarding Microsoft Excel Team Contact ??
http://www.mrexcel.com/forum/showthread.php?p=2518632#post2518632

Regards.

 

[bmurphy]

Save a copy of the wbk without the charts (i.e. delete them).
If that wbk doesn't generate the message, then the charts were the problem.
Rebuild the charts.
If a chart has any item on it referencing a non-existent cell range like #REF, the item with the problem may not be viewable or selectable on the chart, so the chart should be deleted and remade.

For those who might be interested, I've just posted an interesting follow-up:

URGENT Advice Regarding Microsoft Excel Team Contact ??
URGENT Advice Regarding Microsoft Excel Team Contact ??

Regards.