Similar IF OR formula on one sheet works but not on another when they pull from the same data

Copper Contributor

Im using the same formula on two sheets that pull data from another sheet in the workbook. It works on sheet1 but not sheet2.

2 Replies

@tylee2125 Well, why not show the formulas at work in both sheets. One working and one failing. There must be a simple reason for it, but it's difficult to ascertain without seeing the formula and underlying data. Some screenshots or a link to a file sharing site like Onedrive, giving access to the file would be great.


The subject line "Similar IF OR formula on one sheet works but not on another when they pull from the same data" accurately reflects your question. Here's how to troubleshoot the issue:

Troubleshooting Steps:

  1. Double-check the formula:

    • Ensure the formula in both sheets is exactly the same, including cell references, operators, and parentheses. Even a minor typo can cause unexpected behavior.
    • Verify the cell references used in the formula. Are they pointing to the correct cells in the data sheet on both sheets?
  2. Check cell formatting:

    • Confirm that the cells containing the formula on both sheets have the same formatting (number, text, etc.). Mismatched formatting can lead to errors, especially when using functions like IF.
    • Ensure the cells in the data sheet where the formula pulls information have the correct format expected by the formula (e.g., numbers should be formatted as numbers, not text).
  3. Examine circular references:

    • In sheet2, check for circular references. A circular reference occurs when a cell's formula directly or indirectly references itself, causing an endless loop that prevents the formula from calculating correctly.
    • You can use the "Check for Errors" feature (under the Formula tab) to find circular references.
  4. Hidden cell formatting:

    • Although unlikely, hidden formatting might be affecting the formula. You can try clearing cell formatting on both sheets and reapplying the desired format. This can be done by selecting the cells, right-clicking, and choosing "Clear Formats" or "Clear Contents and Formatting".
  5. Formula evaluation order:

    • While rare, in complex formulas, the evaluation order can sometimes lead to unexpected results. Consider simplifying the formula or using parentheses to explicitly define the order of operations if applicable.
  6. Consider workbook corruption (last resort):

    • If none of the above solutions work, it's possible the workbook might be corrupted. Try creating a new workbook and copying the relevant data and formulas to see if the issue persists
    • By systematically going through these steps, you should be able to identify the reason behind the inconsistent behavior of your formulas and get them working correctly across both sheets.