Finding Invalid References

[Roger H]

In Excel 2007 how can I most easily locate invalid references when the following message appears?

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

Urgent help would be greatly appreciated

Roger H

[DonkeyOte]

Roger H, not sure if you still need this answered or not... I'm not an 07 user but in theory this should find and list all formulas returning an error albeit not just ref's pnto a LOG sheet

So assuming you're active on Sheet1 and ran this code a new sheet "Sheet1_Invalid" would be created and in columns A,B & C you would see cell reference, formula & result causing errors -- C showing error type.

Sub form_errors()
Dim s1 As String: s1 = ActiveSheet.Name
Dim rng, cell As Range
Set rng = Sheets(s1).UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
Application.DisplayAlerts = False
Select Case rng Is Nothing
    Case False
        On Error Resume Next
        Sheets(s1 & "_Invalid").Delete
        On Error GoTo 0
        Sheets.Add After:=Sheets(s1)
        ActiveSheet.Name = s1 & "_Invalid"
        Sheets(s1).Select
        For Each cell In rng
            Sheets(s1 & "_Invalid").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = cell.Address
            Sheets(s1 & "_Invalid").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = Replace(cell.Formula, "=", "'")
            Sheets(s1 & "_Invalid").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = cell.Value
        Next cell
End Select
Application.DisplayAlerts = True
End Sub

Assuming 07 remains like 03 for Edit / Find you could look for #REF, looking in Values -- and opt to Find All -- this would list the results but to my knowledge you could not copy & paste the results for review.

[Roger H]

Many thanks for the helpful code which I will hold for future reference

By doing some googling I found that deleting charts can produce the invalid reference

I also found a recommendation for a commercial product which removes invalid references ... I've used it previously and decided to get it again

It worked instantly

So while I am not wanting to name the product others may find it useful to know that there is a way to resolve this reported bug

With Best Wishes and Thanks

Roger H

[newyuppie]

I am having the same problem and would love to find out about the product you talk about. It is only fair that you share with us what worked for you.

Thank you!
P