[Fixed!] Excel Found a Problem with One or More Formula References in This Worksheet

While working with formulas you may make some silly mistakes that were not noticed by you and then you can get the error message saying, “Excel found a problem with one or more formula references in this worksheet”.
But for a large workbook with many worksheets, it becomes difficult to find out the problems and so here we will help you to find the source of this problem easily and rectify it.

For using the wrong cell reference in a formula or wrong external link source or error in charts, pivot tables are the main causes of this error message. We will try to show some easiest ways to fix the errors of our formulas, charts, or pivot tables to get rid of this error message easily in this article.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Fix-1: Using Error Checking Option to Solve Problem with One or More Formula References in This Worksheet

It is quite impossible to find out errors in formulas by checking a large number of worksheets manually. So, here we will show you the easiest way to check out the errors through different sheets of your workbook by using the Error Checking option and for this example consider the following sample dataset with some errors in the formula in the Commission column.

➤ To find the errors go to Formulas Tab >> Formula Auditing Group >> Error Checking Dropdown >> Error Checking Option.

Then, the Error Checking dialog box will open up.
In this box, on the left side, you can see the cell reference containing the error and the source of this error, and to rectify it click on the Edit in Formula Bar option on the right side.

Then, you will be taken to the Formula Bar box to edit this formula.

➤ Replace the formula with the correct formula

Here, D5 is the Sales value and 0.05 is the percentage of commission.

➤ Click on the Resume option.
Then, you will see the next error and fix it like this one. Finally, you will be able to rectify all of the formulas.

Read More: How to Fix #REF! Error in Excel (6 Solutions)

Fix-2: Using Shortcut Key to Check Formulas to Solve Problem with One or More Formula References

Here, we will be using a shortcut key to check on the formulas and rectify them easily to get rid of this error message.

➤ Press CTRL+TILDE ( ͠   ).
Then, the results of the Commission column will show the formulas behind the results, and so you can check out the inconsistent formulas which are the source of the errors in cells E5, E8, E11.

➤ Correct the formulas one by one.

Again press CTRL+TILDE ( ͠   ) to see the results, and now, you will not see that error message again.

Read More: Errors in Excel and Their Meaning (15 Different Errors)

Fix-3: Using Go to Special Option to Find Problems in Formula References

To fix the errors of the formula it is necessary to select the errors at first and to do this prior to changing the formulas here we are going to use the Go To Special option.

➤ Go to the Home Tab >> Editing Group >> Find & Select Dropdown >> Go To Special Option.

Then, the Go To Special wizard will pop up.
➤ Select the Formulas option, check on the Errors option, and finally, press OK.

After that, the cells with errors in the formula will be selected.

➤ Write the correct formula in cell E5

Here, D5 is the Sales value and 0.05 is the percentage of commission.

➤ Then, press ENTER to go to the next cell E8 containing the error and fix the formula. In the same way, correct all of the incorrect formulas.

After fixing them, you will have the correct values and now you will not get that error message again.

Fix-4: Solving Problem with One or More Formula References Using Name Manager

By using the named ranges for the formulas you can easily select the named range containing errors and after deleting them it would be easier for you to solve the problem with this error message.
As you can see, we have the following three named ranges jan_sales, feb_sales, and mar_sales for the Commission columns of the three sheets.

Now, you can easily identify among these ranges which range contains errors.
➤ Go to the Formulas Tab >> Name Manager Option.

Afterward, the Name Manager dialog box will appear.
➤ Click on the Filter dropdown.

➤ Among the options select the option Names with Errors.

Then, the list of the named ranges will be filtered down to the ranges containing different types of errors in the formula, and now, you can delete them to get rid of this error easily.

Read More: Reasons and Corrections of NAME Error in Excel (10 Examples)

Fix-5: Checking External Links to Solve Problem with One or More Formula References

Sometimes we need some values in a formula in a sheet of a workbook from another workbook. To use this value we need to do some external linking and for not using the link properly we can have that error message.
In the following sample dataset, we have summarised the sales values of January, February, March, April. Among them, the first three were in different sheets of the same workbook and the last one was in a different workbook.

Here, we can see the following formula in cell D4 is giving the correct value

Here, Jan!D4, Feb!D4, and March!D4 are interlinkings that represents the values of cell D4 in Jan, Feb, and March sheets, and the last one ‘[April Sales.xlsx]April’!$D$4 is the external linking where April Sales.xlsx is the file name, April is the sheet name and $D$4 is the cell’s reference.

After checking cell D6 we can see that we have used here the wrong workbook name. And so, the external linking is not working properly, and for this reason, we are having the #REF! error here.

To solve these linking problems go to the Data Tab >> Queries & Connections Group >> Edit Links Option.

Then, the Edit Links wizard will open up.
The incorrect links on the left side on which the status Unknown is indicating that these files don’t exist.
➤ Select the first link and then click on the Update Values option to fix the link.

➤ Now, select the correct file name April Sales.xlsx and press OK.

Then, you can see the status is changed from Unknown to OK for the first link. In the same way, fix the other links also.

Eventually, the errors will disappear. And you will get the correct values so that you will not get that error message further.

Read More: REF Error in Excel (9 Suitable Examples)

Fix-6: Solving Excel Found Problem with One or More Formula References in This Worksheet Checking Charts

Sometimes, Charts can be the source of that error message for not selecting data properly.

➤ Keep your mouse on the Plot Area of the chart and right-click on your mouse.

➤ Choose the Select Data option.

Then, the Select Data Source dialog box will appear.
Here, check the selected range in the Chart data range, Legend Entries, and Horizontal Axis Labels carefully if you had selected them properly. Be aware of the fact that if any error is present in the data ranges and if these are not done properly then do them again and rectify the errors.

Fix-7: Checking Pivot Table to Solve Problem with One or More Formula References

Pivot Table can also be the source of this error message if any errors remain in the selected data range of the Pivot Table.

To check the source data range if it contains any error in formulas then go to the PivotTable Analyze Tab >> Change Data Source Dropdown >> Change Data Source Option.

Then, you will be taken to the source data range, and as we can see we have the #VALUE!, #NAME? errors in the Commission column, so, try to fix them, and again create your Pivot Table.

Fix-8: Using VBA Code to Summarise Errors to Fix Them Easily

It would be great if we could have a list containing the types of errors, their position, and the incorrect formulas causing these errors throughout different worksheets, right? So, here with a VBA code, we will try to create this type of sheet to summarise the errors.

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

After that, a Module will be created.

➤ Write the following code

Sub Errorchecking()

Dim x As Integer
Dim sheetname As String
Dim cell, sChar As String
Dim rng As Range
Dim address1 As String
Dim sheetFormula As String, error As String
Dim NewHeaders
NewHeaders = Array("Sheet Name", "Cell Ref", "Error Type", "Formula")

 With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
 End With

 For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "ErrorList" Then
      Worksheets(i).Delete
    End If
 Next i
x = Worksheets.Count
Sheets.Add After:=Sheets(x)
Sheets(Sheets.Count).Name = "ErrorList"

  With Sheets("ErrorList")
    Range("B2:E2") = NewHeaders
  End With
  y = 3
On Error Resume Next

  For i = 1 To x
    sheetname = Worksheets(i).Name
    Application.Goto Sheets(sheetname).Cells(1, 1)
    Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)
    For Each cell In rng
      address1 = cell.Address
      sheetFormula = cell.Formula
      error = cell.Text
      For j = 1 To Len(cell.Formula)
        sChr = Mid(cell.Formula, j, 1)
        If sChr = "[" Or sChr = "!" Or _
          IsError(cell) Then
          With Sheets("ErrorList")
            .Cells(y, 2) = sheetname
            .Cells(y, 3) = address1
            .Cells(y, 4) = error
            .Cells(y, 5) = "'" & sheetFormula
          End With
          y = y + 1
          Exit For
        End If
      Next j
    Next cell
  Next i

  With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
  End With
Sheets("ErrorList").Select
Columns("B:E").EntireColumn.AutoFit
Range("B2:E2").Font.Bold = True
Range("B2").Select
End Sub

Here, the new sheet name is ErrorList, NewHeaders will give the headers in this sheet, the first FOR loop will delete the sheet if its name is ErrorList, then we have declared the position of the headers by the range B2:E2, we have assigned x as the total worksheet numbers and y as 3 for the starting row after the headers.
Three FOR loops will be executed throughout the sheets to extract the Sheet Name, Cell Address, Cell Value, and the formulas in the cells containing different errors.

➤ Press F5.
After that, we will have the following list in a separate sheet named ErrorList. Now, you can easily rectify them to get rid of this error message easily.

Read More: Excel VBA: Turn Off the “On Error Resume Next”

Download Workbook

Conclusion

In this article, we tried to cover the ways to solve the sources of the error message Excel found a problem with one or more formula references in this worksheet. Furthermore, you can use different types of Excel Repair Software such as Wondershare Repairit-File Repair or any other tool as per your choice to fix this error without doing the tasks mentioned in this article. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• [Fixed] Excel Print Error Not Enough Memory
• On Error Resume Next: Handling Error in Excel VBA
• Excel Error: The Number in This Cell is Formatted as Text (7 Fixes)
• How to Remove Value Error in Excel (4 Quick Methods)
• VALUE Error in Excel: 7 Reasons with Solutions