There's a smarter way to handle this, and it starts with rethinking what you're actually asking your spreadsheet to do. The user's problem isn't unique, it's the daily reality for anyone managing a project with shifting teams, changing rows, and a strict template that can't be altered. The manual SUM function works, but it's brittle. It breaks the moment someone adds a line or moves a cell, and it forces you to babysit a formula instead of focusing on the work itself. That's not a workflow; it's a tax.
The good news is that the solution doesn't require redesigning the form or convincing your company to change its standards. The key is to stop chasing the cell and start identifying the value. Instead of writing a formula that depends on a fixed location, you can use a lookup strategy that targets the daily total by its content, like matching the label or the total sum at the bottom right. Tools like INDEX and MATCH, or even QUERY in Google Sheets, can find that specific cell across sheets, even when it moves. The layout stays untouched. The template stays compliant. You just make the formula smart enough to adapt.
What this means for you is simple: you can build a running total that updates itself the moment a new daily sheet is added, without touching a single formula again. No more opening a dedicated sheet to drag a range. No more worrying about whether you missed a row when someone left mid-week. The heavy lifting shifts from manual upkeep to initial setup, and that's a trade worth making. You're not asking for a new tool or a permissions override. You're asking for a formula that respects your constraints while removing the repetitive grind. That's not just doable; it's the kind of small, practical win that makes a real difference in your day.
So here's the concrete takeaway: stop treating your daily total as a moving target and start treating it as a searchable value. Use a formula that finds the cell by what it represents, not where it sits. Test it on one week's worth of sheets, confirm it holds when you add or remove rows, and then let it run. The time you save each week might feel small at first, but over a project's lifetime, it adds up to hours you can spend on actual project decisions. That's the point of automation, not to impress anyone, but to free you up for the work that needs a human.