Master sheet update as new tabs are added

user_15250594 0
Viewed 441

I have a master sheet that I'd like to combine values from other tabs in the same workbook in Google Sheets. The other tabs all have the same format; the data I want to pull into the master is in the same location in all of the tabs. The formula for getting data for tabs that exist is easy. What I want to know is: Is there a way to have the master tab update automatically as new tabs are added to include those new tabs in the maths? Tab names are usually dates, such as 03/15/21, 03/31/21, etc. I only have one other tab in the whole workbook that has different data, which I'd like to exclude. Not sure if this is possible, but thanks in advance to anyone who tries to help.

https://docs.google.com/spreadsheets/d/10F-fqCsEHDeMf221XnA-U2lUocj9O_qHauh00qnq2ew/edit?usp=sharing

google-sheets-formula google-sheets
2 Answers

I've found a workaround, at least for those who don't want to use scripts. I made a column with the dates for the year 01/15, 01/31 ... 12/31, formatted as plain text.

In an adjacent column I put a formula to check a cell in those sheets for a value, in my case: =IFERROR(INDIRECT(A1&"!AC2"),) A1 is where my dates are, AC2 is where the cell in the other tab is that I want to check. Copy down the column (arrayformula will not work here). Basically if the tab exists with the date name in column A, then the value will return, but if the tab doesn't yet exist it will return a blank.

Then, in the next column, I sort and filter the two: =SORT(FILTER(A:B,ISNUMBER(B:B)),2,TRUE) which provides a list of dates for tabs that exist and which updates as new tabs are added (again the data on each tab is laid out identically, which is important!).

I then use that sort/filter column to do my maths on the Master sheet. I realize it's a little complicated, but it works without using scripts. I'll update my sample sheet in case this helps anyone.

user_152505940

Answer

Use Google Apps Script to create a function that sums the value of all sheets.

Step by step

  • Open Apps Script clicking on Tools > Script editor and you will see the script editor. It is based on JavaScript and it allows you to create, access, and modify Google Sheets files with a service called Spreadsheet Service.
  • Write a function with the following features:
    • Input: the cell that you want to sum in A1 Notation
    • Get all the sheets with getSheets
    • Sum the value of your desired cell starting by the second sheet (index 1). Use getRange and getValue.
    • Return the value
  • Call the function from your desired cells in your Spreadsheet. For instance, in E1 use =sumSheets("E1")

Code

function sumSheets(cell){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheets = ss.getSheets()
  var sum = 0
  for (var i=1; i<sheets.length; i++){
    sum += sheets[i].getRange(cell).getValue()
  }
  return sum
}

Reference

user_144099710
Related