I have a budget spreadsheet with tabs for every pay period. These tabs are created as needed and don't have names I can easily know in advance. For instance, one will be "10/15 - 10/28" because that's the pay period. Next month I create a new one with "10/29 - 11/11." I'd like to be able to sum a value across all sheets. For example, every sheet has a row named "Save," some sheets have a row named "Rent", but not every sheet will contain rows with those names and when they do they won't always be in the same cell number.

Desired final tab

Column 1's values are known in advance so those can be hardcoded. For instance, there will never be a random "yet more stuff" appear which I wouldn't sum up by adding a new row to the final tab.

CodePudding user response：

I wrote 2 scripts:

1. budgetTotal which takes a budgetCategory parameter, for example "Rent", and loops through all the sheets in the file to sum up the amounts listed on each sheet for that category.
2. budgetCreation which looks at your Totals sheet and writes these budgetTotal formulas in for each category you have listed.

I ran into a challenge which was, as I added new sheets the formulas wouldn't be aware and update the totals. So, what I did was create a simple button that executes the budgetCreation script. This way, as you add new payroll weeks you just need to press the button and - voila! - the totals update.

There might be a better way to do this using onEdit or onChange triggers but this felt like a decent starting place.

Here's a

The problem with this approach is that the formula won't recalculate automatically when changing any of the source data. In order to do that, see the above method.

Reference:

• onEdit(e)
• Custom Functions in Google Sheets