ICDL Spreadsheets (ICDL): A 3D total that changed after sheet edits

A 3D reference sums the same cell across every sheet in the tab order from the first named sheet to the last.

• Renaming Jun to June is safe: Excel updates references to a renamed sheet automatically, and the sheet stays in the same position in the range.
• Inserting Notes between May and Jul is the dangerous change. Any sheet placed inside the Jan-to-Dec span becomes part of the sum. If Notes!C2 contains a number, it is added silently. If it contains text, it is ignored, which makes the error intermittent and hard to spot.
• Dec (copy) placed after Dec falls outside the range, because the range ends at the sheet named Dec. Its C2 values are excluded, so totals are now too low for those products.

Safer design: do not rely on tab order. Either keep a strict rule that only month sheets sit between Jan and Dec and put all notes before Jan or after Dec, or replace the 3D sum with explicit terms such as =SUM(Jan:Dec!C2) restricted by a controlled sheet list, or use Data > Consolidate with a defined set of references. At minimum, add a check row that compares the Summary total with a manual sum of two or three months, so a silent change is caught immediately.

Use sheet grouping, but treat it as a controlled operation:

1. Click the Jan tab, hold Shift and click the Dec tab. The tabs are now grouped, indicated by [Group] in the title bar.
2. With the group active, insert a row at row 41 on the current sheet and type the product name and any labels. The same edit is applied to all twelve sheets.
3. Ungroup immediately by right-clicking a tab and choosing Ungroup Sheets, or by clicking any single sheet tab. Do not leave the workbook grouped.

Risks to control:

• While sheets are grouped, any cell you type into is overwritten on every sheet. Never edit an existing cell while grouped unless you intend to replace it everywhere.
• Inserting a row shifts formulas on all sheets. Check one month's total formula afterwards.
• Work on a copy first if the workbook is the only version.

An alternative is to build the new row once, copy it, group the sheets and use Paste. This is faster but still carries the same overwrite risk, so ungroup as soon as the paste is complete.

Steps on one month sheet, then applied to the rest:

• Page Layout > Orientation > Landscape.
• Page Layout > Width: 1 page; Height: Automatic. This forces the columns onto one page while allowing as many pages down as the rows need.
• Page Layout > Print Titles > Rows to repeat at top: $1:$1. This repeats the header row on every printed page.
• Set the print area to the used range, and adjust margins or use Narrow margins if the last column spills.

Grouping Jan through Dec before making these settings applies them to all twelve sheets at once, which is much faster than twelve separate setups.

What must still be checked:

• Print Titles is a per-sheet setting. After grouping, open Print Titles on two or three different months to confirm the row reference is $1:$1 on each.
• Check each sheet in Page Break Preview; a long product list may still run to a second page vertically, which is acceptable if the header repeats.
• Add a footer with the sheet name and page number so the printed pack can be reassembled if pages are dropped.