It is twenty to nine on Monday. You open last week's spreadsheet, open Airtable in the next tab, and start again. Export the view, paste it in, drag the formulas down, fix the three that landed on the wrong row, rebuild the chart because it lost its range, tidy the header, send it by ten.
You have done this enough times that it stopped registering as a problem. It is just what Monday is.
It does not have to be. The report does not need rebuilding every week. It needs a structure that lets new data pass through it, and that structure is a single decision made once, on the day you set the thing up: the data and the report live on two different tabs.
The short answer
Sync your Airtable view into a tab that nobody touches. Build the report on a second tab that reads from the first with formulas. Point those formulas at whole columns rather than fixed ranges. Every sync then changes the numbers in your report instead of destroying it.
That is the whole pattern. The rest of this is how to do each part, and where people get it wrong.
Why reports built on the synced tab break
A sync does not politely add rows to the bottom of what you have. It rewrites the tab to match Airtable. Anything built on top of that tab is standing on ground that moves every few hours, and none of the three failures announce themselves.
Formulas end up pointing at the wrong cells. You wrote a subtotal at the bottom of the client list, something like =SUM(E2:E210). The next sync brings 40 new records and the order shifts. Your subtotal now stops somewhere in the middle of the data. It still returns a number. It is the wrong number, and nothing turns red.
Filters quietly drop the new rows. A filter view remembers the range it was created over. New rows land outside that range, the total goes down, and the report looks completely normal. This one costs people whole quarters before anyone notices.
Charts empty out or freeze. A chart bound to A2:B210 keeps looking at A2:B210. If the refreshed data is shorter, the chart shows blanks. If it is longer, the newest records are not in the picture at all.
A synced tab is output. Treat it as a place to work and you are editing something another process is about to overwrite.
Tab one: the sync owns it
Give the sync its own tab and let it have the whole thing. Name it something that tells the truth, like Data, and hold to five rules.
Nobody types in it. Nobody sorts it. Nobody adds a column to it. Nobody deletes a column from it. Nobody builds a chart, a filter view, or a note on it.
Resizing and hiding columns are both safe, because neither changes what sits in which column. Hide the tab, then use Data, then Protect sheets and ranges to restrict editing to yourself. That is the cheapest way to stop a well-meaning colleague from sorting it on a Friday afternoon.
Scope it properly while you are there. Sync a view rather than a whole table, with the filter and the sort already applied in Airtable, so the tab arrives holding the rows you want in the order you want them. The filter logic then stays in the one place your team already maintains, instead of being restated as spreadsheet formulas that drift out of agreement with the view. The four sync methods guide covers view scoping in each method.
Check what your columns arrive as before you build on them. Linked records land as text, rollups land as a number frozen at sync time, attachments land as links with a short life. Which columns survive the trip goes field by field, and attachments get their own post because they catch nearly everyone once.
Tab two: the report reads from tab one
Add a second tab, call it Weekly report, and build the report there entirely out of formulas that read tab one. You never paste. You never type a number that could have been calculated.
Four functions cover almost every report an ops or account manager builds.
| What the report needs to do | Use | Why this one |
|---|---|---|
| Group, total, sort, or filter in one step | QUERY |
One formula produces a whole finished block |
| Show a filtered list of rows, unchanged | FILTER |
Simpler to read and edit than a query string |
| A single total at the top of the page | SUMIF or SUMIFS |
Returns one number into one cell |
| A single count at the top of the page | COUNTIF or COUNTIFS |
Same, for how many rather than how much |
QUERY, for the grouped block
Assume tab one is called Data, column B holds the client, column D the status, and column E the amount.
=QUERY(Data!A:F, "select B, sum(E) where D = 'Active' group by B order by sum(E) desc label sum(E) 'Revenue'", 1)
That reads as: take every row, keep the active ones, one line per client, revenue totalled, biggest first, and call that column Revenue. A whole block of results out of one cell.
Two things to know. The query sits inside double quotes, so text you compare against goes in single quotes. And the final 1 tells Sheets how many header rows sit at the top of the range, which stops it guessing; if your synced tab carries a system row of Airtable field IDs above the visible headers, that number is 2, not 1.
FILTER, for the plain list
When the report is just "show me these rows", FILTER is easier to read six months later.
=FILTER(Data!A:F, Data!D:D = "Active")
Add conditions by adding arguments.
=FILTER(Data!A:F, Data!D:D = "Active", Data!E:E > 5000)
Every condition range has to be the same height as the range being filtered, which is the second reason to use whole columns.
SUMIF and COUNTIF, for the headline numbers
The three or four figures that sit above the table, one per cell.
=SUMIF(Data!D:D, "Active", Data!E:E)
=COUNTIF(Data!C:C, "Overdue")
For a rolling window, SUMIFS takes the range to add up first, then pairs of range and condition.
=SUMIFS(Data!E:E, Data!F:F, ">="&TODAY()-7)
The habit that makes all of it survive
Write Data!A:F, not Data!A2:F210.
A fixed range is a promise about how many records exist, and the record count is exactly what changes when the sync runs. A whole-column reference makes no promise. Two hundred rows on Monday, three hundred and forty on Thursday, ninety after someone archives a project, and the report keeps working because it never claimed to know.
The header row takes care of itself. QUERY gets told about it. FILTER drops it, because the word Status is not equal to Active. SUMIF and COUNTIF skip it for the same reason, and so do the empty rows below the data.
The cost is that Sheets scans more cells, so a very large file recalculates more slowly. Google caps a spreadsheet at 10 million cells across all its tabs, and it gets sluggish well before that. At the size of report most people rebuild on a Monday, you will not notice.
