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.

## Download Workbook

## 8 Fixes for Excel Found a Problem with One or More Formula References in This Worksheet

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

`=D5*0.05`

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 the 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**

`=D5*0.05`

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__: Solve the 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 the 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

`=Jan!D4+Feb!D4+March!D4+'[April Sales.xlsx]April'!$D$4`

Here, ** Jan!D4**,

**, and**

`Feb!D4`

**are interlinkings that represents the values of cell**

`March!D4 `

**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__: Solve Excel Found a 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 the 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

**, then we have declared the position of the headers by the range**

*ErrorList***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”**

## 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.