Google Sheets report breaks when someone edits a column? Fix it

Why does it keep breaking?

Lookups that count columns. Google describes VLOOKUP’s index as “the index of the column in the range that contains the return value.” Insert a column inside the range and column 4 is a different column, with no error. If columns are removed and the index ends up bigger than the range, you get #REF!.
QUERY takes its instructions as text. A query that says “select C, F” still means C and F after someone inserts a column, whatever those columns now hold.
IMPORTRANGE takes its source as text, such as “Sheet1!A1:C10.” If someone rearranges the source file, your report keeps pulling the same address and nothing warns you.
Inputs drift. A “TBC” in a number column, or a date typed three ways, can throw off totals and lookups without an obvious error.
The change leaves little trace. Google warns that cell edit history may not show added or deleted rows and columns, or changes made by formulas.

How do you fix it yourself?

Work on a copy first. Keep your live report running until the new one has given the same answers for a couple of cycles.

Save a named version. Click Last edit at the top right, choose a version, then More > Name this version. A spreadsheet can have up to 15 named versions. To go back, select the version and click Restore this version.
Find the fragile formulas. Click through the report and list every VLOOKUP, QUERY and IMPORTRANGE.
Replace VLOOKUP with XLOOKUP. XLOOKUP takes a separate range to search and a separate range to return, so there is no column number to go stale: =XLOOKUP(A2, Orders!B:B, Orders!H:H). Those are normal references, so they move with the columns.
Make the source a table. Select the data and go to Format > Convert to table. You can then refer to columns by name, as in Google’s example =SUM(DeptSales[Sales Amount]). Set a column type for each column; a Date column, for example, shows a warning on cells that don’t fit. Google says references update when you add or remove data. It doesn’t say what happens when a column is renamed or moved, so test both on your copy.
Separate entry from reporting. Keep one tab for people to type in and another for formulas.
Protect the layout. Go to Data > Protect sheets and ranges, click Add a sheet or range, choose Range or Sheet, then Set permissions. Pick Show a warning when editing this range for a light touch, or Restrict who can edit this range to limit it to named people. When protecting a whole sheet, Except certain cells leaves the entry cells open. Google is clear this “shouldn’t be used as a security measure”: anyone who can open the file can still copy it.
List your text addresses. For each QUERY and IMPORTRANGE, note which columns it depends on, and protect those source columns too.
Add a check cell. At the top of the report, compare two figures that should always agree, such as your report’s total and a plain SUM of the source column. Show the word OK or CHECK.
Turn on change emails. Go to Tools > Notification settings > Edit notifications, choose Any changes are made, and pick Email - daily digest or Email - right away. You can only set this for yourself. For rules about a particular range, Google points to Apps Script.
Write a notes tab. Who owns the sheet, where each number comes from, and what to do when the check cell says CHECK.

Several files feeding each other through IMPORTRANGE is where this stops working: a change in one breaks the others, and each import is capped at 10 MB of data per request.

What are your options?

Option	Good when	Watch for
Do it yourself	One sheet, a capable owner, and a few hours to rebuild and test	The fix depends on that one owner staying
Buy a tool	The report is standard, and a reporting or dashboard product already connects to your data	Someone still has to define the numbers
Hire a freelancer	You want formulas rebuilt by someone who knows Sheets well	Ask for notes, or only one other person understands it
Bring in an outside team (Fixes & connections)	The report draws on several tools and should refresh itself	Needs the people who use the report to explain how they use it

Frequently asked questions

Why does my VLOOKUP return the wrong data after someone adds a column?

VLOOKUP returns a column by its number within the range. Insert a column inside the range and that number points somewhere else, with no error. XLOOKUP avoids this by naming the return range directly.

Does protecting a sheet keep it secure?

No. Google says protection “shouldn’t be used as a security measure.” It helps prevent accidental edits, but people with access can still copy or export the data, and you can’t password-protect it.

Can I see who changed a column?

Version history shows who edited and when. Click Last edit at the top right. A single cell’s history (right-click > Show edit history) may not show added or deleted columns, so use version history for those.

How often does IMPORTRANGE update?

Google says the function automatically checks for updates every hour while the document is open, so a change in the source may not show immediately.