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.

 

[Norie]

Monir

Have you checked the chart(s)?

Where are they getting data from if there's none in the workbook?

If it's from an external source perhaps there's a problem there.

 

[AlphaFrog]

Do you have any Add-Ins? Perhaps an add-in has the "error".

 

[monirg]

Norie;
Yes, I've checked the Charts and everything else in the w/b.
There's no external source. The data is in the w/s "Data" in the w/b.
The w/b works perfectly and as desired (with the exception of the message!).
Regards.

AlphaFrog:
I've Add-Ins, but nothing has changed for a while.
The problem appears to be ONLY associated with this w/b for whatever reason.
Testing my other XL 2003 workbooks does not produce such message on File::Exit.
Regards.

 

[mikerickson]

Is your Personal Macro Workbook a possible source of this error?
Do you use any Names?
What does Error Checking show?

 

[monirg]

Here's something which might be of interest to the experts.

1) In the problem file (with NO VBA, NO Links, NO Forms), under:
... VBE :: Tools :: References :: Available References :
I have 5 references checked:
--> Visual Basic for Application
--> Microsoft Excel 11.0 Object Library
--> OLE Automation
--> Microsoft Office 11.0 Object Library
--> Microsoft Forms 2.0 Object Library

2) For the other (far more complex) w/bs, I DO NOT have the Microsoft Forms 2.0 ... checked!!

3) When I tried to cancel "uncheck" the selected ref (since there're NO "forms" in the project), I got:

Can't remove control or reference; in use

Regards.

 

[Ruddles]

Do this in a copy of your workbook: copy the entire worksheet by right-clicking the tab, select Move or Copy > Create a copy, then delete the original sheet and save.

Any good?

 

[Norie]

Monir

If you are getting that mesage when you try to remove that reference then there might be controls that require the Forms... Library.

Either that or Excel thinks there is.

Try what Ruddles suggests.

You could do it sheet by sheet and after each copy/move see if you still get the same error(s).

You also mention Add-Ins and that's nothing has changed - what hasn't changed and what add-ins are they.

It might be that one of them uses a form.

 

[monirg]

Try what Ruddles suggests.
You could do it sheet by sheet and after each copy/move see if you still get the same error(s).

Tried it. No improvement. Same message on File::Exit

You also mention Add-Ins and that's nothing has changed - what hasn't changed and what add-ins are they.

I did uncheck ALL the Add-Ins in the problem file, saved and closed the file.
Re-opened the file. Made sure there was no Add-Ins checked on the list.
References - VBAProject still showed the same 5 reference libraries.
File Save and Close work fine, while File::Exit triggers the same irritating message (so it is not an add-in related error):

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.

I also ran "XL 2003 Detect and Repair".
No improvement.

Keep in mind that the message is displayed on Exit regardless of which sheet is active, which suggests that the ref to "this worksheet" in the MS Excel message is somewhat misleading!

Deleting any 3 of the 4 sheets, does not change the situation.

I'd be glad to email you experts a proprietary copy of the file (~ 35 KB) to look at.

Regards.

 

[monirg]

Couple of additional observations to my previous reply (# 9):

1) The XL 2003 problem file appears to be updating or calculating something when it is opened.
Open/Close the file, and it will ask if you want to save the changes you made!! despite the fact:
--> nothing has been changed or updated
--> NO VBA, NO Links, NO Forms, NO external data source, and NO w/s dynamic functions are used

2) The same XL 2003 problem file works fine in XL 2007 and Exits without a problem.
That's to say, the MS Excel 2003 message:

"A formula in this worksheet contains one or more invalid references ...."

DOES NOT display on Exit in XL 2007.
(I don't use XL 2007 for many reasons!)
Does the above suggest a bug in XL 2003 ?? ... or not necessarily, and the w/b is likely the problem ??

3) I'd be glad to email you a proprietary copy (~ 35 KB) if interested to look at the XL 2003 problem file and possibly identify the cause of the "invalid references ... " message.
Keep in mind that the invalid ref message appears on (and only on) Exit in XL 2003, not with Save, Save As or Close file.

Regards.