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

Sponsored Links



How to find Invalid Reference in Excel

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 24-04-2009
Member
 
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
  #2  
Old 24-04-2009
Member
 
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

Code:
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
Reply With Quote
  #3  
Old 25-01-2011
Member
 
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
Reply

  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 10: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 04:36 PM
Cell Reference in Excel kamina23 Windows Software 4 08-10-2009 12:53 AM


All times are GMT +5.5. The time now is 07:03 PM.