Invalid References in formula

[ck13]

Hi,

I got this error message when i close my workbook: "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."

The funny thing is that this error message pop out only when i save and
close the workbook on certain worksheets. E.g. I have worksheet a, b, c and
d. When i am either on sheet a and d, i saved the file and close the book at
that sheet, the error message did not pop out. However when I performed
similar actions on either of the other 2 sheets, the error message appear.

Anyone have this problem or anyone knows what could the problem be? I am
running on 2003.

I followed a thread somewhere on this solution by Max and it did not work
out (no issue with the formula and the defined name):

Press F5 > Special. In the "Go To Special" dialog, check "Formulas", then
uncheck all indented options except Errors*, then click OK. This will select
all formulas with errors on the sheet, if any. Either press to delete** all
at one go, or format these cells with say, red fill, for closer individual
inspection / fixing later. Repeat for each sheet ..
*ie uncheck: Numbers, Text, Logicals
**you would probably need to, for eg: re-copy the formulas down from the top
row (assuming these are still good, of course)

If you have defined / named ranges ..

Insert a new sheet, select A1, then click Insert > Name > Paste > Paste List
Check / note the pasted list for any defined range(s) with #REF! errors

Then click Insert > Name > Define, select these defined ranges (one at a
time), and either click to delete (or fix as appropriate within the "Refers
to" box)

---

 

[Luke M]

Fellow sympathizer...

I too have experienced this somewhat sporadic problem. Similar scenario,
working with XL 2003. I get the error when I try to close with certain
sheets visible, no error when closing on others. My workbook does contain
some Named Ranges (dynamic), but no visible errors.

I'll follow this thread, hopefully someone else has some insight.

 

[Dave Peterson]

I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

And if you're working with names, I'd get this, too:
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

The findlink program does a better job of finding bad links (including
references to bad files/folders) and the name manager will make it easier to
clean things up (if you find stuff to clean up in the names).

 

[ck13]

Hi Dave,

Thanks for your response. For some reason (which I do not know), the excel
file does not show the problem after the last weekend. will try out your
suggestion when I faced similar issue again..