The Group Total Read #REF! at Nine, Read a Number by Ten, and Stayed 111,237.15 Light for the Rest of the Year

The workbook is called Maintenance_2026_08.xlsx. Twenty depots, one sheet per depot, and a Summary tab at the front that reads each depot's month-end figure out of the depot's own sheet. It is the shape half the finance workbooks in the world are in, and it has been running since the contractor had six depots.

Every cell in the Summary's figures column holds the same formula, filled down:

=INDIRECT("'"&A2&"'!B18")

Column A holds the depot name. B18 is where the grand total sits on a depot sheet. On the Monday the August pack was due, the column looked like this:

By half past ten the five errors were gone and the group total was a number: 582,571.90. The truth was 693,809.05. Nobody found the missing 111,237.15 until the following March.

Nothing in the workbook had been broken by a formula. Each of the twenty depot sheets footed correctly — every depot's own SUM had expanded over every inserted row exactly as Excel promises. The twenty things that were broken were twenty strings, and a string is the one part of a formula that Excel will never maintain on your behalf.

What this covers. Everything here behaves the same in Excel 2016, 2019, 2021, 2024, Microsoft 365 and Excel for Mac, with the exceptions called out where they arise: XLOOKUP and LET need 365 or 2021 and later, TEXTAFTER needs 365 or 2024. INDIRECT, OFFSET, INDEX, MATCH, SUM, SUMIF, SUMIFS, COUNT, COUNTA, COUNTIF, COUNTIFS, SUMPRODUCT, IF, IFERROR, ISREF, ISNUMBER, NA, ROW, COLUMN, COLUMNS, ADDRESS, CELL, LEN and TRIM work everywhere. A sheet-per-depot consolidation is the example because it is the most common place INDIRECT is reached for, but this is the same job as a dashboard that switches source sheets from a dropdown, a rolling-twelve-months row built with OFFSET, a dependent dropdown list, or any formula anywhere that assembles a reference out of text.

1) Twenty Formulas, One Column, Three Outcomes

The twenty formulas are identical. They differ only in the depot name they concatenate, and they came out three different ways.

Read that last row twice. The failure that covered 62.99% of the file is the one that produced no error, no warning, no colour and no flag — and it is the one that survived, because the only thing a spreadsheet will reliably escalate is an error value.

🎯 Scenario: When a consolidation is built on INDIRECT, the useful question is never "does the summary calculate?" It always calculates. The question is "how would this column look if it were wrong?" If the answer is "exactly like this", the column is not evidence of anything.

2) What INDIRECT Actually Does

INDIRECT takes text and returns the reference that text describes. That is the whole function. =INDIRECT("B18") returns whatever is in B18, and so does =B18, and for a moment they look like two ways of writing the same thing.

They are not, and the difference is the entire article.

When you type =B18, Excel does not store the characters B and 1 and 8. It stores a relationship — this cell depends on that cell — and it maintains that relationship for the rest of the workbook's life. Insert a row above 18 and the formula becomes =B19 by itself. Rename the sheet and every reference to it is rewritten in every formula in the workbook, silently and correctly, including in other sheets and other open workbooks. Cut B18 and paste it four columns over and the formula follows it. You did not ask for any of that. It is the service you are paying for when you use a reference.

When you type =INDIRECT("B18"), Excel stores the three characters B18 as a piece of text and evaluates them, fresh, on every recalculation. There is no relationship to maintain, because there is no relationship: there is a sentence about a relationship. Insert a row above 18 and the sentence still says B18. Rename the sheet and the sentence still names the old sheet. Move the cell and the sentence stays where it is, pointing at whatever moved into the vacancy.

A reference is a relationship Excel maintains. An INDIRECT is a claim about a layout, and nobody maintains a claim.

That is not a defect. It is the point of the function — you use INDIRECT precisely when you want the reference decided at calculation time from something that varies, usually a cell the user picks. What goes wrong is not the function. What goes wrong is that a formula built this way has quietly moved the job of keeping the reference true from Excel to you, and it does not tell you it has done that.

🎯 Scenario: Before you write an INDIRECT, finish this sentence out loud: "this will still be right unless someone ______". If the blank can be filled with "renames a tab", "inserts a row", "sorts the sheet" or "tidies up", you have just written a formula with a maintenance contract attached and no maintainer.

3) The Rename That Errors Loudly

Five tabs were renamed between January and August. Nobody did anything unreasonable:

Every one of those five produced #REF! on the Summary, and #REF! is exactly right: the text 'Chesterfield'!B18 describes a reference that does not exist, and a reference that does not exist is what #REF! means. Not #NAME? — the function name is spelled correctly and the argument is a valid string; the string just points nowhere.

Here is the part worth sitting with. A rename is the one kind of change Excel is best at absorbing. Rename a tab and every real reference to it, everywhere in the workbook, is rewritten for you before you have let go of the mouse. The five people who renamed those tabs had every reason to believe Excel had handled it, because in every other formula in the building, Excel had.

🎯 Scenario: If a workbook's sheet names are load-bearing — if some formula somewhere spells one out inside quotation marks — then renaming a tab is a code change, and it needs to be treated like one. The practical version of that rule: never type a sheet name into a formula. Put it in a cell, in a list, once, where it can be seen and fixed in one place.

4) The Trailing Space Nobody Can See

Tiverton deserves its own section, because it is the one failure in this article that no amount of care at the keyboard would have caught.

The tab is called Tiverton . One space, at the end. In the sheet tab it is invisible — the tab is a little wider than Tiverton needs and that is all. In the Name Box it is invisible. In a screenshot it is invisible. And 'Tiverton'!B18 is not 'Tiverton '!B18, so the reference fails completely.

It is also entirely ordinary in origin: somebody double-clicked the tab, typed the name, and their thumb caught the space bar before Enter.

Two formulas make it visible. On the depot sheet itself:

=TEXTAFTER(CELL("filename",A1),"]")          → "Tiverton "
=LEN(TEXTAFTER(CELL("filename",A1),"]"))     → 9

Tiverton is eight characters. A nine tells you the whole story. (CELL("filename") needs a saved file, and TEXTAFTER is 365 and 2024; on older versions use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255).)

And on the Summary, the test that asks the question directly rather than inferring it from a total:

=ISREF(INDIRECT("'"&$A2&"'!A1"))             → TRUE on 15 rows, FALSE on 5

ISREF is the right tool here and it is almost never reached for. It answers "is this a real reference?" without caring what the cell contains, so it separates the sheet is missing from the sheet is there and the number is odd — the two problems this workbook had, which every other check confuses with each other.

🎯 Scenario: Put =ISREF(INDIRECT("'"&$A2&"'!A1")) in a helper column beside any list of sheet names a workbook drives itself from, and =COUNTIF(C2:C21,FALSE) at the top of it. It answers in one number whether the workbook's own map of itself is still accurate. Here it would have said 5 on the first morning, and 5 is unarguable.

5) The Inserted Row That Errors Never

Now the eight that mattered.

A depot sheet is a simple thing: labels down column A, money down column B, categories subtotalled, grand total at the bottom. As originally built, every depot sheet had its labour subtotal on row 16 and its grand total on row 18, which is why the Summary says B18 twenty times.

During the year, eight depots started hiring plant. Somebody — usually the depot administrator, doing exactly the right thing — inserted a couple of rows and a new block above the total. Six sheets gained one row, two gained two. The grand total moved to row 19 or row 20.

The depot sheet was still correct. This is the part that makes the failure so hard to see: the total formula was =SUM(B2:B17), rows were inserted inside that range, and Excel expanded it to =SUM(B2:B18) automatically. The depot's own sheet footed to the penny. It always had.

Only the Summary was wrong, and it was wrong in the worst possible way: B18 on those sheets is now the labour subtotal.

Every one of those eight numbers is real. It came off the right sheet, out of the right column, in the right currency, for the right month, and it is the right order of magnitude — five of the eight are between 81% and 84% of the figure they replaced, which is to say they are wrong by roughly the size of a plant-hire bill. There is no property of any of those cells that is unusual. There is no formatting tell, no error, no text-versus-number mismatch, no negative, no zero, no outlier. A range check passes. A sign check passes. A "does it look about right" check passes emphatically, because it does look about right.

🎯 Scenario: A reference that points at the wrong row is far more dangerous than one that points at nothing, and the difference is not subtle — it is the difference between a failure you fix before lunch and a failure that ships monthly for seven months. When you address a cell by its coordinates, you are asserting that a human being will never insert a row above it. That assertion has never once held.

6) Why Trace Precedents Shows You Nothing

There is a second reason the eight survived, and it is not about numbers at all. Every tool Excel gives you for auditing a formula is blind to an address inside quotation marks.

• Trace Precedents draws arrows along the dependency tree. An INDIRECT has no precedent to trace beyond the cell holding the name, so the arrow stops at A2 and the depot sheet never appears. The picture is not wrong; it is empty in the exact place you are looking.
• Ctrl+[ (Go To Precedent) has nowhere to go and does nothing.
• Find searching for B18 finds these formulas, but Find searching for what actually matters — which cells depend on this sheet — cannot help you, because the dependency is not recorded.
• The rename fix-up that rewrites every reference when you rename a tab cannot rewrite text. It does not know your text is a sheet name. To Excel it is a string, like "Total" or "kg".
• Formula ▸ Evaluate Formula does step through it, and it is the one tool that works. It is also the one nobody opens on a formula that isn't erroring.
• The dependency tree does not contain the link at all, which is why INDIRECT is volatile: Excel cannot know when the answer might change, so it recalculates it every single time. More on that cost in §12.

So the workbook contained twenty undocumented, untraceable links to twenty sheets, and the only place they were written down was inside the strings themselves.

🎯 Scenario: If you inherit a workbook, Ctrl+F for INDIRECT( and OFFSET( with Look in ▸ Formulas and Within ▸ Workbook before you change anything structural. That search is a list of the assumptions the file is making about its own shape — and it is the only list there is.

7) The IFERROR That Turned a Question Into an Answer

Back to the Monday morning. =SUM(B2:B21) returned #REF!, because SUM propagates errors, and a group total that says #REF! cannot be sent to anybody.

The first instinct was the ordinary one:

=IFERROR(INDIRECT("'"&A2&"'!B18"),0)

And the total became a number: 411,944.40, against a truth of 693,809.05 — 59.37% of it. A number that is 40% wrong, on a page, with no indication of anything.

Then somebody sensible looked at which rows had gone to zero, matched them to the renamed tabs, corrected the five names in column A, and the total moved to 582,571.90 — 83.97% of the truth. At that point every cell in the column held a plausible number, the five loud failures had cost the business nothing at all, and the eight quiet ones had cost it 111,237.15 a month.

The IFERROR is worth being precise about, because it is not a wrong function — it is a right function pointed at the wrong thing. IFERROR(x, 0) says if this cannot be computed, the answer is zero. That is true and useful when a blank lookup genuinely means nothing was sold. It is a lie when the reason the cell errored is that the workbook no longer knows where to look, because in that case the honest answer is not zero, it is "I don't know" — and Excel has a value for exactly that:

=IFERROR(INDIRECT("'"&A2&"'!B18"),NA())

NA() propagates. SUM over a column containing #N/A returns #N/A, charts skip it rather than plotting it as a zero, and no total leaves the building carrying a hole it does not mention. A zero is an answer. #N/A is a question, and the whole reason the eight lasted seven months is that this workbook had been taught to stop asking.

🎯 Scenario: The test for any IFERROR you are about to write is one sentence: is the fallback true? If the fallback is "zero" and zero is a real possible answer, keep it. If the error means the formula has lost its footing, NA() is the fallback, and a total that refuses to add up is the feature you are buying.

8) Read the Label, Not Just the Number

Here is the cheapest fix in this article, and it would have caught both failures on the first morning.

Every depot sheet has a label in column A beside the money in column B. Row 18 was the grand total, so A18 said Total. Pull that as well:

=INDIRECT("'"&$A2&"'!A18")

Filled down twenty rows, that column returns Total seven times, Labour subtotal eight times, and #REF! five times. 7 of 20. One formula, no analysis required, and the answer is not a number you have to have an opinion about — it is a word that either is or is not the word you expected.

Then make the figure itself depend on it:

=IF(INDIRECT("'"&$A2&"'!A18")="Total",INDIRECT("'"&$A2&"'!B18"),NA())

Now a depot that has had a row inserted reports #N/A instead of its labour subtotal, the group total reports #N/A instead of 582,571.90, and somebody has to go and look. Thirteen rows would have failed loudly on the first morning instead of five, and thirteen is the correct number.

Or with LET, which evaluates the sheet prefix once instead of three times:

=LET(sheet, "'"&$A2&"'!",
     label, INDIRECT(sheet&"A18"),
     IF(label="Total", INDIRECT(sheet&"B18"), NA()))

🎯 Scenario: Any formula that reaches into a layout should carry a second formula that checks the layout is still the one it was written for. The number tells you what the cell contains; the label tells you whether the cell is the cell you meant. The label is the only one of the two that can catch a row insertion, and it costs one column.

9) Stop Addressing the Row. Find It.

The label check turns a silent failure into a loud one, which is a large improvement and still not the fix. The fix is to stop pinning a row number at all: don't say the total is on row 18, say the total is the row that says Total.

=INDEX(INDIRECT("'"&$A2&"'!B:B"), MATCH("Total", INDIRECT("'"&$A2&"'!A:A"), 0))

Or, from 2021 onward:

=XLOOKUP("Total", INDIRECT("'"&$A2&"'!A:A"), INDIRECT("'"&$A2&"'!B:B"), NA())

This survives every insertion, deletion and re-sort on the depot sheets. All eight of the quiet failures would have returned the right number automatically, on the first morning and every morning since, with nobody told and nothing to fix — because the formula is now looking for a thing rather than a place, and the thing did not move.

Note carefully what is left. INDIRECT is still there, and it has to be: the sheet name genuinely varies per row and that is the one job only INDIRECT can do in a formula. The five renames would still have errored. But that was always the failure mode that worked properly.

Two refinements worth the keystrokes:

• Match the label exactly and make it unique. If a sheet has "Total" in A18 and "Total labour" in A16, MATCH("Total",…,0) is fine — it is an exact match, not a wildcard. But if two rows both say exactly Total, MATCH takes the first, which may not be the last. Label the grand total something that appears once: Depot total.
• Prefer a named cell on each sheet. If every depot sheet has a sheet-scoped name DepotTotal on its total cell, the Summary becomes =INDIRECT("'"&$A2&"'!DepotTotal") — and a name is a reference Excel maintains, so it follows the cell through insertions, deletions and cuts. You are still exposed to the tab being renamed, and to the name being deleted, but the row number is gone from the formula entirely, which removes the failure that cost 111,237.15.

🎯 Scenario: The general rule that comes out of this whole file: address data by what it is, not by where it sits. A row number is a fact about today's layout. A label, a name, or a table column is a fact about the data. One of those survives a colleague being helpful and the other does not.

10) OFFSET Is the Same Bargain in Different Words

OFFSET gets recommended as the non-text alternative to INDIRECT, and it does avoid the string. It does not avoid the problem, because the problem was never the string — it was pinning a position that a human can move.

OFFSET(reference, rows, cols, [height], [width]) starts at a reference and walks a fixed number of rows and columns from it. The anchor is a genuine reference, so Excel maintains it. The counts are numbers, and Excel does not maintain numbers.

The Summary's rolling-twelve-months row was built this way:

=OFFSET($B$4, 0, MONTH($B$1)-1)

B4 is January, twelve months run across, and the formula steps to the current month. Then a column was inserted for a new cost category between B and C. $B$4 became $C$4 automatically — Excel maintained the anchor perfectly, exactly as advertised. The MONTH()-1 stayed MONTH()-1, because it is arithmetic and there is nothing in it to maintain. The formula now steps eight columns from the wrong starting point and reads September's cell for August, and it will keep doing that until somebody notices that the year-to-date row disagrees with the year-to-date column.

The comparison, laid out plainly:

INDEX is the honest replacement for OFFSET in nearly every case, and it is not volatile:

=OFFSET($B$4, 0, MONTH($B$1)-1)      → volatile, anchored, breaks on insert
=INDEX($B$4:$M$4, MONTH($B$1))       → not volatile, and the range moves with the sheet

The second one does not need a first-column trick either: INDEX is 1-based over the range you hand it, so month 8 is argument 8, which is one fewer thing to get wrong than OFFSET's zero-based step.

🎯 Scenario: If you are reaching for OFFSET to pick the nth item out of a row or column, INDEX does it, does it without volatility, and does it with a range that Excel keeps true. Keep OFFSET for the one thing INDEX cannot do without help: returning a block of a given height and width whose size is itself computed.

11) Where INDIRECT Genuinely Earns Its Place

None of this makes INDIRECT a bad function. It does one thing nothing else does, and there are four jobs where it is the right answer.

1. A reference the user chooses. A dashboard with a dropdown of sheet names, or a region selector that drives which named range gets summed:

=SUM(INDIRECT($B$1))          where B1 is a dropdown of "North", "South", "East"

This is INDIRECT doing exactly what it exists for. The text it evaluates lives in a cell, the cell is validated against a list, and the list is maintained in one place — which is the whole difference between this and typing sheet names into twenty formulas.

2. Dependent dropdowns. Data Validation cannot take a formula that spills, but it can take =INDIRECT($A2), so a Category cell holding Fasteners makes the Item list resolve to the named range Fasteners. This remains the standard two-level dropdown in every version of Excel, and there is no simpler way to do it.

3. Deliberately freezing a range against insertion. This is the one case where INDIRECT's blindness is the feature:

=SUM(INDIRECT("B2:B100"))

That range will be B2:B100 forever. Insert rows, delete rows, it does not care. If you have a form or a template where the summing range must stay put no matter what a user does to the rows inside it, this is the tool, and it is the only tool. Just write a comment next to it saying so, because the next person will otherwise read it as a mistake — and in nineteen cases out of twenty they would be right.

4. R1C1 and computed addresses. INDIRECT's second argument switches the text to R1C1 style, which is the tidy way to express a relative step:

=INDIRECT("R[-1]C", FALSE)        the cell above, whatever cell this is
=INDIRECT(ADDRESS(ROW()-1, 4))    D, one row up, in A1 style

The common thread in all four: the text is either supplied by a cell the user controls or generated from ROW()/ADDRESS() at calculation time. In none of them is a layout coordinate typed into a formula by an author and then left there for three years. That — the hard-coded, hand-typed, never-revisited coordinate — is what cost this contractor 111,237.15 a month, and it is a habit rather than a function.

🎯 Scenario: The line to hold is not "never use INDIRECT". It is "never let INDIRECT be the only record of where something lives." If the sheet name is in a cell and the row is found by label, the function is doing real work and nothing is hidden. If the sheet name and the row number are both inside the quotation marks, you have written an undocumented dependency with a typed-in coordinate at the end of it.

12) Two Costs You Pay Even When It Works

Volatility. INDIRECT and OFFSET are volatile functions, alongside NOW, TODAY, RAND, RANDBETWEEN, CELL and INFO. A volatile function recalculates on every recalculation of the workbook, whether or not anything it depends on changed, because Excel cannot know what it depends on. Everything downstream of it recalculates too.

At twenty formulas this is free. It stops being free fast: a 5,000-row model with three OFFSET columns is 15,000 volatile cells plus their dependents, recalculating on every single edit anywhere in the workbook, including edits on other sheets. This is the most common cause of the workbook that pauses for two seconds after each keystroke. INDEX and XLOOKUP are not volatile and will usually replace both.

The closed workbook. This one produces the most support tickets of anything in this article:

=INDIRECT("'C:\Reports\[Depots.xlsx]Summary'!B18")

That works perfectly while Depots.xlsx is open, and returns #REF! the instant it is closed. A normal external link — ='C:\Reports\[Depots.xlsx]Summary'!B18 — keeps working, because Excel caches the last known value of an external reference inside your file. INDIRECT has no cached value to fall back on: it has to resolve the reference right now, and you cannot resolve a reference into a workbook that is not loaded. "It worked yesterday and today it's all #REF!" almost always means someone closed the source, and no amount of reopening and recalculating will fix the formula — only opening the source will.

A few more boundaries worth knowing, all of them the same shape:

• INDIRECT accepts defined names and structured references as text — =SUM(INDIRECT("Table1[Amount]")) works — so it can reach a table by name.
• A sheet name containing a space, a hyphen, or any punctuation must be wrapped in single quotes inside the text, which is why the formula in this article is "'"&A2&"'!B18" and not A2&"!B18". Quote it always; it costs four characters and it is free insurance against the day a depot is called Ellesmere Port.
• An apostrophe in the sheet name itself has to be doubled: a tab called Bill's Yard needs 'Bill''s Yard'!B18.
• Passing INDIRECT a range of names to evaluate as an array works in 365 and is unreliable before it. Use a helper column instead of being clever.

🎯 Scenario: If a workbook has become sluggish and nobody can say why, count the volatile functions before you optimise anything else: Ctrl+F for OFFSET(, INDIRECT(, TODAY(), NOW(). The fix is rarely faster hardware and is usually INDEX.

13) Five Checks

Run these on any workbook that assembles references out of text. The first two take a minute and would have caught everything in this article on the first morning.

1. Does the cell you address still say what you think?

=INDIRECT("'"&$A2&"'!A18")                        → "Total" on 7 rows of 20

The single highest-value formula here. It catches the rename (#REF!) and the moved row (Labour subtotal) in one column, and the answer is a word rather than a number you have to judge.

2. Does every name the workbook drives itself from still exist?

=ISREF(INDIRECT("'"&$A2&"'!A1"))                  → FALSE on 5 rows
=COUNTIF($C$2:$C$21,FALSE)                        → 5

This separates "the sheet is gone" from "the number looks odd", which is the distinction every other check blurs.

3. Does the consolidation foot to something computed a different way?

The only check that catches a wrong-row read, because those values are valid numbers with nothing wrong with them. Total the detail by a route that does not use INDIRECT — a Power Query append of the twenty sheets, a flat transaction table, or the sum of the depots' own totals read by label — and compare. 693,809.05 against 582,571.90 is not a rounding difference.

4. Does each line move the way the business moved?

=B2/prior_month                                   → 0.41 to 0.84 on eight rows, ~1.00 on seven
=COUNTIF(ratio_column,"<0.95")                    → 8

Eight depots simultaneously reporting a fifth less than last month is not a trading pattern, it is a layout change. A ratio column against the prior period is the cheapest structural-break detector there is, and it does not need to know anything about how the formulas work.

5. How many assumptions about layout does this file contain?

Ctrl+F, Look in ▸ Formulas, Within ▸ Workbook, and count: INDIRECT( → 20, OFFSET( → 1. That is 21 undocumented claims about where things live, and it is the list to check before renaming a tab, inserting a row or sorting a sheet — not afterwards.

14) Twelve Traps

1. A reference is maintained; a string is not. =B18 follows its cell through insertions, renames and cuts forever. =INDIRECT("B18") says B18 until somebody edits the characters by hand.
2. A rename breaks it loudly and a row insertion breaks it silently, and the silent one is the expensive one. Five #REF! cost ninety minutes; eight wrong rows cost seven months and 111,237.15 a month.
3. A wrong row returns a real number. From the right sheet, the right column and the right period, at 41% to 84% of the truth. No range check, sign check, type check or format check can see it.
4. The source sheet can be perfectly correct while the summary is wrong. All twenty depot sheets footed to the penny, because their own SUM ranges expanded over the inserted rows exactly as they should.
5. A trailing space in a tab name is invisible and fatal. 'Tiverton'!B18 is not 'Tiverton '!B18. =LEN(TEXTAFTER(CELL("filename",A1),"]")) is how you see it.
6. Trace Precedents, Ctrl+[ and the rename fix-up are all blind to a quoted address. The dependency is not in the dependency tree, which is also why the function is volatile.
7. IFERROR(…,0) on a broken reference is a false statement. Zero is an answer; the honest fallback is NA(), which propagates into the total and forces somebody to look.
8. Pull the label, not just the number. Reading A18 as well as B18 catches both failure modes for the price of one column.
9. Find the row, don't address it. INDEX/MATCH or XLOOKUP on the label survives every insertion; a sheet-scoped defined name on the total cell survives insertions, deletions and cuts.
10. OFFSET is the same bargain as INDIRECT. Excel maintains the anchor and ignores the counts, so an inserted column moves the start and leaves the step behind. INDEX is the non-volatile replacement.
11. INDIRECT cannot read a closed workbook. A normal external link caches its last value; INDIRECT must resolve now, so it returns #REF! the moment the source closes.
12. Quote the sheet name every time — "'"&A2&"'!B18" — and double any apostrophe inside it. It costs four characters and covers every depot that turns out to be called Ellesmere Port.

None of the twenty people in this story did anything wrong. The depot administrators inserted rows into their own sheets to record spend that was really happening, and their sheets stayed correct. The five who renamed tabs were tidying, or coding the estate, or opening a second Grimsby, and had every reason to expect Excel to follow them, because Excel follows them everywhere else. Whoever wrote the Summary in the first place solved a real problem in the obvious way and produced a column that worked for years.

What made it expensive is that the workbook's knowledge of its own shape lived in twenty strings, and a string cannot be maintained, cannot be traced, cannot be renamed with the thing it names, and will never once complain. Every other dependency in a workbook announces itself: a real reference draws an arrow, a broken name goes #NAME?, a deleted cell goes #REF! in colour. A typed-in coordinate announces nothing, because it isn't a dependency as far as the file is concerned. It is prose.

So the discipline is small and it is not about avoiding a function. Let the variable part of the reference live in a cell — one list of sheet names, maintained once, visible to everyone. Let the fixed part be found rather than counted: a label, a defined name, a table column, anything that is a fact about the data rather than a fact about this morning's layout. And where a formula must make an assumption about a layout, put a second formula next to it whose only job is to say whether the assumption still holds, and let that one fail loudly. Do those three things and a consolidation tells you the day it stops being true. Skip them and it tells you in March, with seven months of packs already sent.