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]

Hi Rory;

Select one of the chart sheets, then bring up the names dialog and you will see what I mean.

I've just done that. I could see NO problem whatsoever.
Went back to w/s "Data" and changed the values in the controls. Charts' displays remain perfect.
Are you referring to the w/b: *-5.xls ??

Regards.

 

[RoryA]

I am referring to the workbook you sent me. Those 4 names contain #REF! errors.

 

[RoryA]

In the last one you just sent, the RowData and ColumnData global names still have #REF! errors (note: I am referring to the workbook-level names, not the sheet level ones) but the other two have been corrected.

 

[monirg]

Hi Rory;

I'm looking at the SAME copy of the XL 2003 file I sent you (*-5.xls).
It works fine and I don't see any problem or #REF! anywhere (a part from the Invalid ref message on Exit)!
Q1: Which XL version are you running ??
Q2: What do you see displayed (as we speak) in the 2 charts ??

Regards.

 

[RoryA]

I'm running 2003; I don't see the error on exit, but the names are incorrect in the workbook you sent.

 

[Norie]

Do you have any other workbooks open?

Should there be any other workbooks open?

When you do have other 'related' workbooks open do you still get the error?

 

[monirg]

Rory;

I'm running 2003; I don't see the error on exit, but the names are incorrect in the workbook you sent.

Again, I've thoroughly tested the SAME XL 2003 file I sent you (*-5.xls).
It works fine, and I don't see any problem or #REF! anywhere (a part from the Invalid Ref message on Exit)!

1) If the situation is not confusing enough at this point, here's something else!
Following your latest reply:
--> I copied the same file I sent you (*-5.xls) to a 3rd computer (running XL 2003, Win XP SP3)
--> Opened the file, checked ALL named ranges, checked the charts, searched for errors, etc.
.... (your) #REF! nowhere to be found. Everything looked perfect
--> Went back to w/s "Data" and changed the values in the controls. Everything corresponded and updated nicely, including the charts
--> File::Save & File::Close worked fine
--> but File::Exit replicated the SAME "Invalid references.." message!!!!!!!

2) So on my two computers (both running XL 2003):
>> w/b works perfectly
>> named ranges are correct
>> NO problem with File::Save & File::Close
>> PROBLEM with File::Exit
Now, with the SAME XL 2003 file *-5.xls I sent to you:
How you had "no error on Exit and the range names are incorrect" (your XL 2003 version) ??


Norie;

Do you have any other workbooks open?

NO.

Should there be any other workbooks open?

NO.

When you do have other 'related' workbooks open do you still get the error?

There're NO 'related" workbooks, and I get the error with & without 'unrelated" w/b open.

Regards.

 

[RoryA]

In your workbook, you have three versions of the ColumnData name. One is defined for worksheet ColList, one for Data and there is one workbook-level one that refers to:
=OFFSET(INDIRECT(#REF!$I$4),0,0,COUNTA(#REF!$A:$A)-6,1).
Similar for RowData, where the workbook-level version refers to:
=OFFSET(INDIRECT(#REF!$I$3),0,0,1,COUNTA(#REF!$10:$10)-1)

If you select either of the Chart sheets before invoking the Names dialog, you should see this.

 

[monirg]

Rory;

I've only one definition of each named range in the w/b, as it should be:

ColumnData::=OFFSET(INDIRECT(ColList!$I$4),0,0,COUNTA(Data!$A:$A)-6,1)
RowData::=OFFSET(INDIRECT(ColList!$I$3),0,0,1,COUNTA(Data!$10:$10)-1)

Could you please provide the full "three versions of the ColumnData" for example ??

Maybe we're getting closer!

Regards.

 

[RoryA]

I've only one definition of each named range in the w/b, as it should be:

No, you haven't. There are three in the workbook you sent me. I strongly suggest you download Jan Karel Pieterse's Name Manager add-in to help you find them.