Trace errant formula to stop annoying message

[Mark Stephens]

Hi,

Every now and then my worekbook throws up a dialogue box with the following
warning 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'


Short of ploughing through every page and searching every cell containing
formula, does anyone know an easier way of locating the culprit formula?

Thanks and regards, Mark

 

[Peter T]

I would start by looking for whatever is the most likely cause of that
message.

Eg, if invalid cell references are suspect, search (ie Edit Find) cells
containing #REF! with "Look in: Formulas" checked. After sorting out those
repeat with "Look in: Values" That error occurs when a cell ref is entirely
deleted with delete rows/columns, or a sheet is deleted. Range names can be
similarly affected.

Regards,
Peter T

 

[Mark Stephens]

Thanks Peter, unfortunately didn't come up with anything, any other ideas?

regards, Mark

 

[Peter T]

So you are saying there are no #REF! errors in cell references or in Defined
Names.

Any broken links perhaps. Look in Edit / Links

Regards,
Peter T

 

[Charlie]

Try This

Sub FindBadCells()

Dim Sheet As Worksheet
Dim Cell As Range

For Each Sheet In ThisWorkbook.Worksheets
For Each Cell In Sheet.UsedRange
If IsError(Cell) Then MsgBox Sheet.Name & ", " & Cell.Address(0, 0)
Next Cell
Next Sheet

End Sub

 

[Mark Stephens]

Thanks Charlie, got them all very efficiently, thankuverymuch, kind regards,
Mark

 

[Peter T]

Curiosity, when you say "got them all" what sort of error cells did you find
that was triggering your message.

FWIW you could use the SpecialCells to find error cells, manually or
programmatically.

Regards,
Peter T

 

[Mark Stephens]

Hi Charlie,

They were links to a workbook I deleted and all resided in the same kind of
sheets in the same place, but because they were out on a limb (column 35 or
something, I couldn't begin to locate them manually, thanks again, regards,
Mark

 

[sam anco]

Hows this for a Microsoft answer!

SYMPTOMS
When you delete an item from a table that is bound to a chart in
Microsoft Office Excel 2007, you may receive the following 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.
After you correct the references that are not valid in the table, you
may continue to receive this error message.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products
that are listed in the "Applies to" section.

------------------------------------------------------------------------
--------

APPLIES TO
• Microsoft Office Excel 2007

Back to the top

Keywords: kberrmsg kbtshoot kbchart kbprb kbexpertisebeginner KB931389


Todate they have no solution, if anyone can fix this then I would be
grateful

 

[richardp]

Sam,

I have experienced this problem and have a workaround that has always
got rid of the problem for me. Unfortunately it takes a bit of time.

The problem occurs when you delete a chart that is linked to data in
your workbook (possibly only when the data is on a different sheet from
your chart).

The invalid references are linked somehow to the area of the sheet that
contained the deleted chart and you cannot get rid of them even if you
delete the entire rows.

What you need to do is this (where the shett containing the error is
named 'MySheet':

1. Backup workbook.
2. Rename the sheet that is causing the problem 'MySheetOld'
3. Create a new sheet 'MySheet' in your workbook.
4. Copy the rows in 'MySheetOld' down to but not including the area that
contained your deleted chart.
5. Paste into exactly the same rows in 'MySheet'.
6. Copy any rows below (but not including) the area in 'MySheetOld'.
7. Paste into exactly the same rows in 'MySheet'.
8. Change any names that point to 'MySheetOld' to point to 'MySheet'
9. On all other sheets in the workbook find and replace 'MySheetOld' in
formulae with 'MySheet'.
10. Save workbook.
11. Delete sheet 'MySheetOld'.
12. Error message should no longer appear.

You may have to play about with this a bit if you are not sure where tha
deleted chart was. Usually you can work your way down a sheet, forcing a
recalculate (grouping rows is a good and accurate way to do this - just
progressively add rows to a group then close and open the group). When
you get to the rows that the deleted chart was in the error message will
start appearing.

Steps 6 & 7 may be problematic as you may not know where the corrupted
rows end.

Hope this all works for you.

Regards,
Richard

Two points to Microsoft if they read this:

1. This problem occurs in both 2003 and 2007.
2. It is high time that this problem was fixed.

 

[richardp]

Sam,

A little more on this.

If you find that you still get the error message when you have followed
these steps, restore your backup and start again. Repeat all steps
except step 11. In some cases, deleting the old sheet somehow passes the
problem to the new sheet. Don't ask me how that works!

In this case, delete all the data (use Edit/Clear/All), formatting, etc
on the old sheet. Do not delete the rows. Do not delete the sheet. Then
just hide the sheet and don't use it. Your old sheet contains nothing
now except the unresolved problem so it is very little in the way of
overhead in your workbook.

Your new sheet should now work ok.

Richard

 

[Barb Reinhardt]

I'm seeing this problem when I run a macro which copies data from the
workbook but so far, am unable to reproduce it. I should be able to figure
out which worksheet/s are affected, but how do I get it to show up again.
Select the rows and copy? It's not clear from your last response.

Thanks,
Barb Reinhardt

 

[Dimitris]

Hello Charlie,

Thanx for the macro! It worked for me too
I had a huge spreadsheet and when there were no data, some cells were
dividing with 0.

However, after I fixed everything there is still a minor problem. I run
the macro and nothing appears, which means that everything works fine.
When I enter data and then delete them, the irritating message pops up
again!
The macro does not find any invalid cells, so I guess it's Microsoft's
problem....

Anyway, thanx for the macro again!

Dimitris

 

[David Lean]

Thank you. You may laugh at the Microsoft post. but it was exactly what I needed to solve my issue.

Now I know it is a bug in 2007, I can program around it. It was killing me that when I edited a the data series & changed it to an identical value the error went away.

SO now I can modify the chart to point to a range I'm not deleting. Then delete the rows the chart was using. Then point the chart back to its original position.
& Bingo 1 workaround.

(I'm unsure if this is fixed in Excel 2010, but as I want my app to work on 2003, 2007 & 2010 it doesn't really matter.

Thanks again