There is a problem with this formula Excel mac

1. 04-11-2019, 12:16 PM #1

   Inhaltsverzeichnis Show

   •  User Overview:
   • What Does Excel Found A Problem With One Or More Formula References In Worksheet Error Means?
   • Reasons Of  “Excel Formula References Error”:
   • How To Fix “Excel Found A Problem With One Or More Formula References” Error?
   • Fix 1# Isolate The Specific Spreadsheet Tab
   • Fix 2# Check External Links
   • Fix 3# Check The Name Manager
   • Fix 4# Check Your Charts
   • Fix 5# Check Your Pivot Tables
   • Fix 6# F2 – See formulas in Excel
   • Fix 7# Control + ~ To show all Formulas
   • Fix 8# Trace Precedents and Trace Dependents
   • Automatic Solution: MS Excel Repair Tool
   • Steps to Utilize MS Excel Repair Tool:
   • Tips To Minimize The Occurrence Of Excel Formula Errors
   • Conclusion:

   I'm pulling what's left of my hair out. I have an existing, and working VLOOKUP function that every time I try to edit, it gives me the "There is a problem with this formula error." I can't figure it out and I've tried everything, including re-creating it on another new tab. here is the formula: =CONCATENATE("bridge group "|VLOOKUP(B281|MPLS|6|TRUE)) B281 = dropdown list of col 1 in MPLS table array Sample of MPLS table array: Albany NY, ALBY, x.x.x.237, x.x.x.170, 3020, ALBY-78539a I'm trying to change the match approximation to FALSE, but any changes I make to the formula cause the error indicating col_index_num as unrecognizable. Again, this works right now as is, but it's set for approximation and returns a bad result for one of the options. I'm just trying to change it to exact match and cannot.

   Please help!

2. 04-11-2019, 12:18 PM #2

   Administrative Note:

   Welcome to the forum.

   

   We would very much like to help you with your query, however the thread title does not really convey what your request is about. Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

   Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

   (Note: this change is not optional. As you are new here, I have done it for you this time.)

   Ali
   
   Enthusiastic self-taught user of MS Excel who's always learning!
   Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.

   

   Forum Rules (updated September 2018): please read them here.

   
   How to use the Power Query code you've been given: help here. More about the Power suite here.

3. 04-11-2019, 12:19 PM #3

   Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch. 1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

   2. Make sure that your desired results are also shown (mock up the results manually).

   3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

   4. Try to avoid using merged cells as they cause lots of problems.

   Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

   Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

   

4. 04-11-2019, 12:20 PM #4

   Hello and welcome to the forum. You use "|" instead of "," for formula separators? That's different. I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data. To upload an Excel workbook, follow these steps: 1) Click on "Go Advanced" 2) Click on "Manage Attachments" 3) Click on "Choose File" 4) Choose your file and click on "Open" 5) Click on "Upload"

   6) Click on "Close this window"

5. 04-11-2019, 12:30 PM #5

   Do you have "|" set as your list separator in Windows Regional Settings? That's the only way using "|" as a formula separator would work, and I haven't actually tested it. If you are moving from one computer to another, and the list separator in Regional Settings does not match, that would explain why the error is occurring.

6. 04-11-2019, 12:59 PM #6

   Yes, | is the delimiter set. On the sample data I added the commas for clarity of the different fields. I would have screen captured but the full IP's cannot be shown publicly. The error is specifically indicating the index field, claiming that using any number (1-6 and I tried them all) is somehow "unacceptable". =CONCATENATE("bridge group "|VLOOKUP(B281|MPLS|6|FALSE))

   This is what I'm trying to change, and it highlights "6" as an error, which makes no sense - especially since it's working now. Again, it does this for ANY VLOOKUP function including typing a new one from scratch.

7. 04-11-2019, 01:27 PM #7

   The issue is actually with the named range. Named ranges use "," as the separator, even if it is set differently in Regional Settings. I believe this is causing the error. If you set the range to $B$1:$G$6, for example, you don't get the error. It's not ideal, and I'm not sure what the workaround is, or even if there is one.

8. 04-11-2019, 01:36 PM #8

   AHH HA! That was it. Thank you greatly!

9. 04-11-2019, 01:40 PM #9

   No problem, happy to help!

Are you guys looking for some easy tricks to fix “Excel found a problem with one or more formula references” error? Well in that case this blog will surely help you to get rid of this persistent Excel error message.

So, read the complete tutorial and try all the given solutions for fixing up Excel formula references error. Before diving into the solution let’s run a quick eye over the scenario which many users like you complaining about.

 User Overview:

Upon saving my Excel sheet, I receive the following error message:

We found a problem with one or more formula references in this worksheet.

Check that the cell references, range names, defined names and links to other workbooks in your formulas are all correct.

“Error Checking” (under “Formulas” → “Formula editing”) returns

The error check is completed for the entire sheet.

What is the fastest way to find what is causing this problem?

Excel in Office 365 ProPlus, Windows 7.

source:

https://superuser.com/questions/1003139/what-causes-excel-to-raise-an-error-about-one-or-more-formula-references-when

To fix corrupt Excel file, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
3. Preview the repaired files and click Save File to save the files at desired location.

What Does Excel Found A Problem With One Or More Formula References In Worksheet Error Means?

In Excel, formulas play a great role and thus many experts consider that Excel is all about formulas. Though Excel provides so many formulas and functions to work flawlessly in this application. But sometimes silly mistakes in these formulas start showing annoying errors like “Excel found a problem with one or more formula references in worksheet”.

Error declaration:

“Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.”

Screenshot of the error:

Reasons Of  “Excel Formula References Error”:

The reason behind getting such an error can be the following:

• when your Excel spreadsheet formula having a wrong cell reference
• maybe you have used object linking or link embedding OLE to the application which is not working.

Like this, there are many more reasons behind such Excel Formula References Error.

After catching a complete idea about the root causes of this excel error. Now it’s high time to run down into the fixes section. So that you can easily troubleshoot “Excel found a problem with one or more formula references”.

How To Fix “Excel Found A Problem With One Or More Formula References” Error?

Fix 1# Isolate The Specific Spreadsheet Tab

If in case you have an enormous number of files with so many tabs then start isolating some specific tab that is generating this error message. Well to perform this you just need to copy your tabs from the old workbook to a new one. Keep checking which tab in the new workbook throws the error message.

Press the save option after copying down each of the tabs. As it is seen that error message usually appears at the time of saving the event.

Fix 2# Check External Links

The second option to fix “Excel found a problem with one or more formula references” is by picking out the external links having errors.

To check out the external links containing errors, you just need to follow this step:

In your Excel application, go to your Data Tab >> Queries & Connections >> Edit Links.

If you encounter any fault in the Excel external links then remove it.

Fix 3# Check The Name Manager

Another very effective way to troubleshoot Excel found a problem with one or more formula references error is by checking out the name manager.

For this, you just need to scroll through the Name Manager (it’s a dialog box that helps you to easily manage the entire of your named ranges). If any formula associated with your Named Ranges is having an error (ie #N/A, #REF, #VALUE!, #NAME?, #DIV/0).

Then delete all the named ranges one by one which is showing such error. and after every deletion also check whether your action fixes the issue or not.

The second option is to filter out the Named Ranges having errors. Well, it is quite a faster way to check out the error showing Name Manager.

Fix 4# Check Your Charts

It’s a quite difficult task to track down which excel chart object is actually causing this error. Mainly when you have so many charts on the given tab.

Generally, it is seen that if a chart is having some kind of error then it will pop-up after you select the chart or when you insert the Select Data Source dialog box.

Below here is the list of area locations where you need to check for errors.

• In the source data of the chart.
• in each of the chart series formula present within the Select Data Source dialog box.
• Check for the errors in the horizontal axis formula which is present inside the Select Data Source dialog box.
• Also, check-in the chart’s Secondary Axis.
• Also check in the linked Axis Labels, Data Labels, or in the Chart Title.

If in case all the above trick fails to identify the error causing chart. Then in that case try Copy/Pasting the chart and watch out whether the new copy is also showing the same error message.

Fix 5# Check Your Pivot Tables

Chances are also that problem is somewhere residing in your Pivot Table’s Data Source formula. So, choose your Pivot Table and go to the PivotTable Tools >> Analyze >> Change Data Source >> Change Data Source…

Now, here you need to check out whether any of your formula is having the error or not.

Check Formulas in Excel

Sometimes small and silly formula errors in excel can also lead to big consequences. Whether you have misplaced the comma or entered a wrong reference; all these will severely hamper your results. So, to avoid such a situation it’s better to check your formula and make it error-free. Learn how it is to be done:

Fix 6# F2 – See formulas in Excel

For checking out the formula of some specific cell:

Firstly, you need to choose the cell after then press the F2 key from your keyboard.

This will highlight the cells referenced in the formula. After then you can make easy check and editing of your excel formulas.

Fix 7# Control + ~ To show all Formulas

Choose any cell of your excel spreadsheet and from your keyboard press the Ctrl and tilde ( ~ ) button simultaneously.

This will present you with all the formulas having on your worksheet. To go back to the normal view just press the Ctrl and Tilde ( ~ ) button once more.

You can get this tool also from the Formula Auditing group present within the Formula tab.

Fix 8# Trace Precedents and Trace Dependents

In Excel, Precedents is used for the cell referenced in the formula. Suppose you are using a formula =C6+7 then in that case C6 is a precedent.

Whereas, in Excel terms, Dependents is used for the cells which depend on its content from another cell. If the above-mentioned formula is written within the cell C9. Then in that case C9 will dependent on C6.

For showing precedents in your workbook cell:

• choose the cell having formula and tap to the Formula
• Now from the Formula Auditing group select the Trace Precedents.
• Arrow will indicate the precedent cells. You can toggle this feature off just by tapping on the Remove Arrows option of the same Formula Auditing group.

For using the Dependents in your workbook cell:

• Choose the cells which are already referenced in some other cell’s formula.
• Now go to the formula tab and then choose the Formula Auditing Group.
• Hit the Trace Dependents option. This will figure out the cells which depend on that particular cell.

Automatic Solution: MS Excel Repair Tool

Besides trying the manual solution to fix Excel found a problem with one or more formula references error. If unfortunately, none of the above solutions works to fix it. In that case, chances are also that may be any of your Excel objects is somehow got corrupted. And thus, it is showing such formula references error.

To deal with such a situation we always recommend our users to go with reliable and professional MS Excel Repair Tool. This is the best repair utility to fix all sorts of issues, corruption, and even errors in the Excel file. With the help of this, the users can repair the Excel file easily and also restore the entire corrupted data including cell comments, charts, other data, and worksheet properties. it is the best professionally designed program to easily repair corrupt .xls and .xlsx files.

* Free version of the product only previews recoverable data.

Steps to Utilize MS Excel Repair Tool:

Tips To Minimize The Occurrence Of Excel Formula Errors

Here are some few helpful tricks to minimize the occurrence of Excel formula errors. so, read it and do follow it.

• Start every formula in Excel by putting an equal sign in the beginning.
• Make use of the * symbol to apply multiplication, not an X.
• Check out the opening and closing parentheses that they are in pairs or not.
• Put quotation marks in formulas around the text.

Conclusion:

I have tried my level best to cover all the fixes to troubleshoot this “Excel Found A Problem with One Or More Formula References” Error. but if you come across some other issue within your Excel spreadsheet then please let us know just by asking it in our comment section.