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.
by Allen Wyatt
(last updated July 3, 2015)
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:
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.
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!
Remember your number line from your early years in school? Some numbers can be below zero (negative numbers) and others above ...Discover More
Do you see some small rectangular boxes appearing in your formula results? It could be because Excel is substituting that box ...Discover More
Calculating an average of a group of numbers is easy. What if you want to exclude a couple of the numbers from the group you ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.