One tab per month, each with about 300 rows and the same block of eight formulas at the top summarising that month. Every time the business changes a category I edit eight formulas twelve times, and I always miss one. The tabs exist because my manager wants to click a month and see the month. Is there a way to do this that does not involve me copying formulas around all afternoon?
The pattern is that the twelve tabs are the bug. Keep one long table with a date column and every row of every month in it, then build a single summary that filters by month, and your eight formulas exist once. Everything else you can do is a way of maintaining a mistake more efficiently.
Then keep the long table hidden and make each month tab a view of it with one QUERY at the top. He still clicks a month, you still have one set of logic, and nobody has to have an argument about it.
I agree in principle but the monthly tabs are not negotiable, they are how the person who signs things off works.
If you truly cannot restructure, put every changeable value in a config tab and have all twelve tabs reference it. Category names, thresholds, date boundaries, all in one place, and the formulas themselves stop needing edits because only the inputs change. It is not as clean but it turns twelve edits into one.
Do both in order. Config tab this week, long table the first time somebody asks for a year on year comparison and the monthly tabs cannot give it to them.
A config tab is something I can build this afternoon without asking anyone for permission, which matters more right now than the ideal answer.
One table, many views. Every reporting file that hurts is a file where somebody stored data in the tab names.
I maintained twenty four tabs like this for two years and the cost was never the editing, it was the year one of the tabs quietly diverged and nobody noticed for four months. If you keep the tabs, at least add a check row that compares each tab's total against a total from the raw data.
Sheets has no equivalent of selecting several tabs and typing once, which is what people coming from Excel are looking for. Closest you get is a template tab plus a short script that pushes the formula block into every sheet whose name matches a pattern. Ten lines, runs on demand, and it is honest about what it is doing.
@csv_apologist · last mo.
Seconding the hidden long table, because I lived the other version for two years. The tabs stay, the numbers come from one place, and the moment a category changes you edit one formula. The only real work is the migration, which for twelve tabs of three hundred rows is an afternoon with a stacked range.
Reply
Report