Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Cross-sheet sum after rename

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Summary still references Mar!B2 after the sheet was renamed to March → #NAME?. Fixed Jan+Feb+Mar → 33 (LibreOffice verified).

About this data: Made-up units for Ananya Joshi (Pune) and Layla Haddad (Dubai). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Renaming sheets silently breaks sheet-qualified formulas.

What: Broken Summary B4 #NAME?. Fixed 33.

How:

  1. Open Summary.
  2. Check sheet tabs.
  3. Point formula at the real sheet name.
  4. Confirm 33.

Ravindra Bagale’s Tip

After any rename, audit formulas for the old sheet name.

Bugs explained (with mini examples)

Bug 1 — Stale sheet name

Mar! after rename to March.

Bug 2 — No rename checklist

Always search formulas for old name.

Practice tasks (12)

  1. Task 1. Broken B4?
    Hint: #NAME?.
  2. Task 2. Expected?
    Hint: 33.
  3. Task 3. Fix formula.
    Hint: Update sheet name.
  4. Task 4. Interview line.
    Hint: Rename broke cross-sheet sum.
  5. Task 5. Pune row is B2.
    Hint: Yes.
  6. Task 6. Add Apr sheet.
    Hint: +Apr!B2.
  7. Task 7. Find/Replace Mar!.
    Hint: Yes.
  8. Task 8. Why fragile?
    Hint: Rename/order.
  9. Task 9. Bonus: careful with INDIRECT.
    Hint: Volatile.
  10. Task 10. Document policy.
    Hint: A5.
  11. Task 11. Save.
    Hint: Yes.
  12. Task 12. Recheck Feb.
    Hint: 11.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Align sheet names with formula.
  2. Total 33. LO: #NAME? → 33.

Quick quiz

Q1. Renaming a sheet can break:
Q2. Fixed LO total:
Q3. Broken LO result:
Q4. Best habit after rename:
Q5. Jan!B2 means: