Error dialog box meaning and how to find error

[Guest]

I have a spreadsheet that has formulas and links to another page in the
spreadsheet. The other spreadsheet has a link to another workbook. On the
2nd worksheet of the work book I get an error message box "A formula in this
worksheet contains one or more invalid references. Verify that your formulas
contain a vaild path, workbook, range name, and cell reference. I have
reviewed every single cell, cleared all formats and formulas in all the
unused cells. The message is not consistant about appearing. I continually
have problems with my data changing on me after I save and close. It is a
very large spreadsheet. would hate to have to recreate. Is there anyway for
me to get Excel to tell me where the error is? It only allows me to click
ok. It does not come up with the circular reference box to check my formulas.

 

[Dave Peterson]

I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm
to search for links to the other workbooks.

And Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

To look for problems in the names.

 

[MarkFred]

I think this error can have several causes and can be hard to fix.

But, here is what I found when this showed up in some stuff I was doing.

I have some complicated excel templates with several sheets and embedded VBA
code. I use these templates to process relatively large data sets. The
templates run, import data, do processing, create output and "save as" with a
unique name.

I built these in excel 97 and 2003 and they ran just fine in either version.

When I run them in excel 2007, I get the message "A Formula In This Workbook
Contains One Or More Invalid References." I looked and looked, but could not
find any bad references. All the cells computed properly.

Eventually, I opened the Selection Pane and noticed under "shapes on this
sheet" an object called 'Chart 1' listed on certain sheets that did not have
any such objects visible. I deleted these and the error went away.

Not sure what thes were or how they got there. I never saw any evidence of
the object at all in the older excel versions.

Anyway, maybe you could check for this?