I85PREP
⌘ K
MO-210 study materials
MO-210 study resources

The 3D Reference That Never Grew

MO-210 · Put your knowledge to work in context.

All case studies

The 3D Reference That Never Grew

Your team maintains MonthlyOps.xlsx: twelve data sheets named Jan through Dec, all with an identical layout, plus a Summary sheet whose cell C5 is =SUM(Jan:Dec!C10). In March a colleague inserts a sheet called "Q1 Notes" at the far right. In April she adds a thirteenth data sheet, "Dec Adjustments", also at the far right, and expects its C10 to roll into the total - it does not. Last week she renamed the Dec sheet to December; Summary!C5 now returns #REF!. She wants to keep adding ad-hoc sheets in any month without ever editing C5 again. The workbook must keep working in Excel 2016, no macros, no Power Query, no data model, and it is shared so protecting workbook structure is not an option.

1. Why is "Dec Adjustments" excluded from C5, and what structural change would make any future sheet count automatically without editing the formula again?

Read the worked answer

Why it happens. A 3D reference such as =SUM(Jan:Dec!C10) sums C10 on every sheet that sits between Jan and Dec in tab order, inclusive. "Dec Adjustments" was placed to the right of Dec, so it is outside the endpoints. "Q1 Notes" is outside too - which is probably what you want, and is a clue that position, not content, decides membership.

The fix that survives future additions.

  • Insert a sheet named Start to the left of Jan and a sheet named End to the right of Dec. Both must be empty in the summed cells (or their C10 values get added).
  • Change C5 to =SUM(Start:End!C10).
  • Every new data sheet is inserted between Start and End, so it is picked up with no formula edit.

Caveats to state out loud. Renaming either marker sheet breaks the formula (see next task). Hidden and very-hidden sheets between the markers are still summed. If the team would accept it, a single consolidated table with a Month column plus SUMIFS is more robust than 3D references.

2. Why did renaming Dec to December produce #REF! when ordinary references usually follow a rename, and what is the fastest repair plus the precautions?

Read the worked answer

Why. Excel rewrites plain references (=Dec!C10 becomes =December!C10) when a sheet is renamed, but it does not rewrite a 3D reference whose endpoint sheet is renamed. Jan:Dec no longer resolves, so the whole formula fails.

Fastest repair.

  1. If the rename just happened, Ctrl+Z restores both the name and the formula.
  2. Otherwise select C5 and retype =SUM(Jan:December!C10) - the tab name is now December, not Dec.
  3. Recalculate and sanity-check one month against its own C10.

Precautions.

  • Use the Start/End marker idea from the previous task: the markers never get renamed, so renames of real data sheets are harmless.
  • Standardise sheet names before the workbook is shared.
  • Rename back only deliberately: renaming December to Dec breaks the repaired formula in exactly the same way.
  • Tell the team that a #REF! in a summary formula is a naming problem, not a data problem.

3. A colleague uses Move or Copy to send only the Summary sheet into QuarterlyReport.xlsx. Totals appear at first, then later show #REF! or stale numbers. Explain the behaviour and give a procedure that produces stable quarterly figures.

Read the worked answer

What actually transfers. A sheet containing SUM(Jan:Dec!C10) copied to another workbook becomes an external reference: the formula turns into something like =SUM('C:\Reports\[MonthlyOps.xlsx]Jan:Dec'!C10). The visible numbers come from the cached value in the copied workbook.

Why it later breaks.

  • If MonthlyOps.xlsx is moved, renamed, or unavailable, the link cannot refresh and Excel shows #REF! or the last cached figure.
  • Cached values look correct, so nobody notices the report is quietly frozen.

Procedure for a stable snapshot.

  1. In MonthlyOps, copy the Summary range.
  2. In QuarterlyReport, use Paste Special > Values (or Paste Special > Values and Number Formats) so the numbers are real numbers.
  3. Keep a note of the source file and date in the report.

If a live link is genuinely required, use Paste Special > Paste Link, keep both workbooks in the same folder, never rename them, and check File > Info > Edit Links before publishing.