Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Thread Tools Search this Thread
Old 24-04-2009
Join Date: Dec 2007
Posts: 93
How to find Invalid Reference in Excel

Hi friends,

I am using Excel 2003 and many a times i am getting the following error message as "A formula in this worksheet contains one or more invalid references" and it takes a very long time to find out those references is there any any way where i can easily find out Invalid Reference in my excel sheet.
Reply With Quote
Old 24-04-2009
Join Date: Jan 2008
Posts: 3,751
Re: How to find Invalid Reference in Excel

I was also getting same issue since i have deleted some of the formula from my Excel sheet, see if that you have also made same mistake or else you can use this code too

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"
        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
Reply With Quote
Old 25-01-2011
Join Date: Jan 2011
Posts: 1
idea Re: How to find Invalid Reference in Excel

I just spent four hours trying to fix the same error in a work book with 36 work sheets. I finally fixed it by selecting all worksheets in the workbook and copying them to a new workbook. I had to hit "OK" 36 times before, but all the errors went away after this. Try it.
Reply With Quote

  TechArena Community > Software > Windows Software
Tags: , , ,

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads for: "How to find Invalid Reference in Excel"
Thread Thread Starter Forum Replies Last Post
Getting Error "invalid API object reference" after upgrading to Mac OS X Lion 10.7.4 Mr. Anderson Operating Systems 4 14-05-2012 09:10 PM
Pivot table invalid data source reference error in Microsoft Excel CeLeStiA MS Office Support 2 25-02-2012 10:51 AM
Is it possible to get rid of circular reference in Excel sheet Iyyappan MS Office Support 2 21-02-2012 12:59 PM
New virus Defense Center, can't find any reference on google Narmad Networking & Security 5 20-07-2010 03:36 PM
Cell Reference in Excel kamina23 Windows Software 4 07-10-2009 11:53 PM

All times are GMT +5.5. The time now is 10:25 AM.