SOLVED

Why do I keep getting a pesky formula error message?

Brass Contributor

RESOLVED!


I'm working on a large file, with plenty of worksheets, formulas, named ranges, and macro's. I keep getting a message there's a formula error in my worksheets. I run the error tracking on each and every one of the worksheets, and there are no errors found. I've double (and triple and quadruple) checked my formulas, and they're fine. I've checked to make sure none of my named ranges have errors, and the macro's don't have any issues. I've also checked all my charts, and none of them have any errors, yet the message persists.

 

Can anyone suggest anything else I need to be checking? Thanks in advance.

7 Replies

@rjoberhofer 

It sounds like you've already taken many steps to troubleshoot the issue, but sometimes formula errors can be elusive. Here are a few additional suggestions you might consider:

  1. Hidden Cells or Rows with Errors: Check if there are any hidden cells or rows that contain errors. Even if they're not visible, they can still trigger the error message. Unhide all rows and columns and check for any cells with errors.
  2. Conditional Formatting Rules: Sometimes conditional formatting rules can inadvertently cause errors to appear. Check if there are any conditional formatting rules applied to cells that might be causing issues.
  3. External References: If your workbook contains external references to other workbooks or data sources, make sure those references are valid and not causing any errors.
  4. Array Formulas: If you're using array formulas, double-check that they are entered correctly and evaluate correctly.
  5. Circular References: Circular references can sometimes cause unexpected errors. Check if there are any circular references in your workbook and resolve them.
  6. Data Validation: If you're using data validation, make sure that the criteria are set up correctly and aren't causing any conflicts with your formulas.
  7. Formula Precedents and Dependents: Use Excel's "Trace Precedents" and "Trace Dependents" features to identify any cells that are influencing or influenced by the formulas in question.
  8. Worksheet Properties: Check the worksheet properties, including any defined names, data tables, or protection settings, to ensure they are configured correctly.
  9. Save and Reopen: Sometimes saving the workbook, closing Excel completely, and then reopening it can resolve mysterious formula errors.
  10. Isolate the Issue: Try to isolate the worksheets or sections of the workbook where the error message is appearing to narrow down the source of the problem.
  11. Activate the Inquire add-in in Excel, you'll need to follow these steps:
  • Open Excel.
  • Go to the "File" tab in the ribbon.
  • Click on "Options" at the bottom of the left-hand menu. This will open the Excel Options dialog box.
  • In the Excel Options dialog box, select "Add-Ins" from the left-hand menu.
  • In the "Manage" dropdown menu at the bottom of the dialog box, select "COM Add-ins" and then click on the "Go" button.
  • This will open the COM Add-Ins dialog box. Check the box next to "Inquire" in the list of available add-ins.
  • Click "OK" to enable the Inquire add-in.
  • After enabling the Inquire add-in, you should see a new tab called "Inquire" appear in the Excel ribbon.

Once the Inquire add-in is activated, you can start using its features to analyze and review the structure of your Excel workbooks. Keep in mind that the availability of the Inquire add-in may vary depending on your version of Excel and your Office 365 subscription plan.

By systematically going through these additional checks, you may be able to identify the root cause of the formula error messages in your workbook. The text was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

@NikolinoDE thanks for the quick response. I ran thru all your suggestions except the one for the COM add-in. Using Excel for Mac, I can't get that one....apparently it's not available.

 

I deleted my 'report' tabs one at a time just to see if the error messages would stop, but no luck going that way. I use a hidden tab where most of the calculations that appear in the 'report' tabs are displayed, but in deleting that tab, everything is an error. I also checked for hidden links and error messages (#REF) in those links. I found a few; deleted them, but the error message continues.

 

Stumped still, but I certainly do appreciate your expertise.

 

Rick

best response confirmed by rjoberhofer (Brass Contributor)
Solution

@rjoberhofer 

Inquire add-in is not available for Excel on Mac, so far I know.

let's explore some additional troubleshooting steps to address the persistent formula error messages:

  1. Check for Data Types: Ensure that the data types in your cells are consistent with the formulas you're using. For example, if you're performing calculations on numbers, make sure that the cells containing those numbers are formatted as numbers and not as text.
  2. Review Formula Auditing Tools: Excel for Mac includes built-in tools for auditing formulas. You can use features like "Trace Precedents" and "Trace Dependents" to identify any cells that are influencing or influenced by the formulas in question. This can help you pinpoint the source of the errors more accurately.
  3. Evaluate Formulas: Use the "Evaluate Formula" feature to step through your formulas and see the intermediate results at each stage of calculation. This can help identify where the errors are occurring and why.
  4. Use Error Checking: Excel for Mac includes error checking features that can help identify and resolve formula errors. Go to the "Formulas" tab in the ribbon and click on "Error Checking" to see if there are any errors detected in your workbook.
  5. Recreate Formulas: If you're still unable to identify the source of the formula errors, consider recreating the formulas from scratch. Copy the data to a new worksheet and rebuild the formulas one by one, checking for errors as you go.

By systematically going through these additional troubleshooting steps, you may be able to identify and resolve the formula error messages in your workbook. If you continue to experience difficulties, Welcome to your Excel discussion space!. My knowledge of Mac is limited.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

@NikolinoDE Thanks for your persistence. I think it would be impossible for me to recreate all the formulas, as there are literally thousands. I've tried the formula audits; Tracing dependents and precedents isn't practical either, as I would have to know which formula or reference is giving me the error. Again, there are thousands, and the formula error tracking does not identify any specific formulas or cells that are creating the error.

Would you know of any other superstar Excel user who, for a fee, would take my file, open it in a PC environment to use the other tools you mentioned to find the issue? (payable via Venmo when the issue is resolved)

Rick

@rjoberhofer 

It seems you are not really looking for a solution.

You do not show the "pesky error message".

You do not provide the workbook.

So the helpers have no way of knowing what is going wrong.

 

The file is 109mb in size and won't fit in the upload portal. My original question asked for ideas on things I should look for, other than the numerous error checking routines I already did.

There is a gold contributor who has the file now who's taking a look at it. Hopefully he can figure it out.

thanks for the reply.
ALL IS RESOLVED! Thanks to NikoLinoDE for his expertise.
1 best response

Accepted Solutions
best response confirmed by rjoberhofer (Brass Contributor)
Solution

@rjoberhofer 

Inquire add-in is not available for Excel on Mac, so far I know.

let's explore some additional troubleshooting steps to address the persistent formula error messages:

  1. Check for Data Types: Ensure that the data types in your cells are consistent with the formulas you're using. For example, if you're performing calculations on numbers, make sure that the cells containing those numbers are formatted as numbers and not as text.
  2. Review Formula Auditing Tools: Excel for Mac includes built-in tools for auditing formulas. You can use features like "Trace Precedents" and "Trace Dependents" to identify any cells that are influencing or influenced by the formulas in question. This can help you pinpoint the source of the errors more accurately.
  3. Evaluate Formulas: Use the "Evaluate Formula" feature to step through your formulas and see the intermediate results at each stage of calculation. This can help identify where the errors are occurring and why.
  4. Use Error Checking: Excel for Mac includes error checking features that can help identify and resolve formula errors. Go to the "Formulas" tab in the ribbon and click on "Error Checking" to see if there are any errors detected in your workbook.
  5. Recreate Formulas: If you're still unable to identify the source of the formula errors, consider recreating the formulas from scratch. Copy the data to a new worksheet and rebuild the formulas one by one, checking for errors as you go.

By systematically going through these additional troubleshooting steps, you may be able to identify and resolve the formula error messages in your workbook. If you continue to experience difficulties, Welcome to your Excel discussion space!. My knowledge of Mac is limited.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

View solution in original post