Share via

Formula not working

Sharon O 0 Reputation points
2026-06-15T14:05:44.9833333+00:00

Hello. I am having problems with some calculations. I have 2 sheets on this workbook. I am bringing forward a total from sheet 1 on to sheet 2. This is part of a further calculation, which is where the problem is.

To be exact, I need to deduct a total on sheet 2 from this number that I am bringing forward to get an overage or shortfall amount. This was working yesterday and now it isn't.

Microsoft 365 and Office | Excel | For home | Windows
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sharon O 0 Reputation points
    2026-06-15T14:59:16.8733333+00:00

    User's image

    Was this answer helpful?

  2. Sharon O 0 Reputation points
    2026-06-15T14:52:31.92+00:00

    I have tried everything, including starting a new workbook. Still the same issue

    Is it possible to send the workbook to someone to look at and hopefully fix?

    Was this answer helpful?

  3. DaveM121 890.1K Reputation points Independent Advisor
    2026-06-15T14:42:35.4333333+00:00

    Press F9 or Shift + F9, does that cause the formula to recalculate correctly?

    1

    If it does, on the top menu in Excel, on the Formulas tab, click Calculation options and check if that is set to Automatic.

    2

    If the shortcut keys do not force the calculation, please provide a screenshot of the formula you are using in both cells on the worksheets.

    Was this answer helpful?

    0 comments

  4. AI answer

    2026-06-15T14:05:57.01+00:00

    Try these checks step by step on the workbook:

    1. Confirm the formula is still a formula
      On Sheet2, click the cell that should show the overage/shortfall and look in the formula bar:
      • It must start with =. For example:
        =Sheet1!B10 - Sheet2!B10
      • If it shows plain text like Sheet1!B10 - Sheet2!B10 or SUM(A1:A10) without =, re‑enter it with an equal sign.
    2. Check the sheet reference
      Make sure the formula still points to the correct sheet and cell:
      • Example: =Sheet1!B10 to bring the total from Sheet1.
      • If Sheet1 was renamed or deleted, the formula may show #REF!. In that case, edit the formula and reselect the correct cell on Sheet1.
    3. Verify cell formats (Text vs Number)
      If any of the cells in the calculation are formatted as Text, Excel will not calculate:
      • Select the cells involved (on both sheets).
      • Go to Home → Number Format and set them to General or Number.
      • Press F2 in each formula cell and then Enter so Excel recalculates.
    4. Check for manual calculation mode
      If calculation is set to Manual, formulas will stop updating:
      • Go to File → Options → Formulas.
      • Under Calculation options, ensure Automatic is selected.
      • Click OK, then check if the overage/shortfall updates.
    5. Look for formula syntax issues
      • Ensure parentheses are paired correctly if using functions like IF, SUM, etc.
      • Example of a correct formula:
        =Sheet1!B10 - Sheet2!B10
        or
        =IF(Sheet1!B10 - Sheet2!B10<0,"Shortfall","Overage")
    6. Check for deleted or changed source cells
      If the total on Sheet1 was moved or deleted:
      • The formula may show #REF!. Edit the formula, remove #REF!, and select the correct source cell again.

    If the formula still does not work after these checks, recreate it from scratch on Sheet2 by typing = and then clicking the total cell on Sheet1, typing -, then clicking the total cell on Sheet2, and pressing Enter.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.