Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Tracking Down Invalid References.

Tracking Down Invalid References

by Allen Wyatt
(last updated December 24, 2018)

Joel noted that when he closes a workbook that has thousands of formulas in it he is getting this message lately: "A formula in this worksheet contains one or more invalid references." Joel wonders how he can know which of the seven worksheets in this workbook is being referred to. How can I find the errant formula? I do not observe any problems in the display of information on my reports.

Tracking down invalid references can be frustrating. There are several places you can start to look. The first is in the formulas that are on the worksheets. (Yes, you need to do these steps for each worksheet in the workbook.) Use the Go To Special dialog box (press F5 and choose Special) to choose to go to only the cells that contain errors. You can then use the Tab key to move amongst any cells that Excel selects.

You could also use the Find tool to look for possible errors. Just press Ctrl+F to display the Find tab of the Find and Replace dialog box, then search for the # character. Make sure you tell Excel to do its searching within Formulas. Inspect anything that is found to see if it is an error or not.

You should also take a look at any named ranges defined in your workbook. Look at each name in the Define dialog box (Insert | Name | Define), making sure that whatever is in the Refers To box doesn't include any error indications.

These aren't all the places that there could be errors; Excel is really good at letting errors exist in lots of places. If you need to search for errors often, you might try a macro that looks through your formulas for any potential errors.

Sub CheckReferences()
' Check for possible missing or erroneous links in
' formulas and list possible errors in a summary sheet

  Dim iSh As Integer
  Dim sShName As String
  Dim sht As Worksheet
  Dim c As Cell
  Dim sChar As String
  Dim rng As Range
  Dim i As Integer, j As Integer
  Dim wks As Worksheet
  Dim sChr As String, addr As String
  Dim sFormula As String, scVal As String
  Dim lNewRow As Long
  Dim vHeaders

  vHeaders = Array("Sheet Name", "Cell", "Cell Value", "Formula")
  'check if 'Summary' worksheet is in workbook
  'and if so, delete it
  With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
  End With

  For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Summary" Then
      Worksheets(i).Delete
    End If
  Next i

  iSh = Worksheets.Count

  'create a new summary sheet
    Sheets.Add After:=Sheets(iSh)
    Sheets(Sheets.Count).Name = "Summary"
  With Sheets("Summary")
    Range("A1:D1") = vHeaders
  End With
  lNewRow = 2

  ' this will not work if the sheet is protected,
  ' assume that sheet should not be changed; so ignore it
  On Error Resume Next

  For i = 1 To iSh
    sShName = Worksheets(i).Name
    Application.Goto Sheets(sShName).Cells(1, 1)
    Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)

    For Each c In rng
      addr = c.Address
      sFormula = c.Formula
      scVal = c.Text

      For j = 1 To Len(c.Formula)
        sChr = Mid(c.Formula, j, 1)

        If sChr = "[" Or sChr = "!" Or _
          IsError(c) Then
          'write values to summary sheet
          With Sheets("Summary")
            .Cells(lNewRow, 1) = sShName
            .Cells(lNewRow, 2) = addr
            .Cells(lNewRow, 3) = scVal
            .Cells(lNewRow, 4) = "'" & sFormula
          End With
          lNewRow = lNewRow + 1
          Exit For
        End If
      Next j
    Next c
  Next i

' housekeeping
  With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
  End With

  Set wks = Nothing
  Set sht = Nothing
  Set rng = Nothing

' tidy up
  Sheets("Summary").Select
  Columns("A:D").EntireColumn.AutoFit
  Range("A1:D1").Font.Bold = True
  Range("A2").Select
End Sub

This macro creates a worksheet called "Summary" that is used to list information about any errors detected in the worksheet links.

You can also use Excel MVP Bill Manville's FindLink program, which does an amazing job of locating information in links. You could use the add-in to search for the # character in all your links, which should help you locate the errors. More information on FindLink can be found here:

http://www.manville.org.uk/software/findlink.htm

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8662) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Tracking Down Invalid References.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Setting Up an Array with Fields

One of the more esoteric ways to display data is with an "array," which is like a miniature inline table. This tip ...

Discover More

Removing Add-ins

Add-ins are used to extend Excel's capabilities in lots of different ways. If you want to get rid of an add-in ...

Discover More

Horizontally Viewing All Your Text

If you are viewing a document and your text runs off the right side of the document window, it can be a real bother to ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Returning the Smallest Non-Zero Value

In a series of values you may need to know the smallest value that isn't a zero. There is no built-in function to do ...

Discover More

Deriving High and Low Non-Zero Values

When analyzing your numeric data, you may need to figure out the largest and smallest numbers in a set of values. If you ...

Discover More

Pulling Formulas from a Worksheet

The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 9?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.