0

Basically, I am trying to make a master tab that adds up all of the data from all the other tabs. These tabs are weekly breakdown numbers for ad statistic numbers, so there is a new tab added every week. Here is what every tab looks like. enter image description here The only option I have in mind to accomplish this is adding the new tab name to the formula (to be written) every time I create a new tab. However, I am wondering if there is a way to reference all tabs that contain a hyphen "-" because they are all titled with a date range separated by a hyphen. Is there any way to achieve this? Maybe even with some sort of helper column on the to-be-created master tab?

1

function SHEETLIST() {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1 ) ;
  out[0] = [ "NAME" , "#GID" ];
  for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = 
  [sheets[i-1].getName() , sheets[i-1].getSheetId() ];
  return out
}
catch( err ) {
  return "#ERROR!" 
}}

=QUERY(SHEETLIST(TODAY()), "select Col1 where Col1 contains '-'", 0)

0

  • This is fantastic! How do I get SHEETLIST() to update automatically when I add a new tab? Also, how could I format the tabs pulled from SHEETLIST() into an array to use with a query formula? EX: =QUERY(['tab 1'!A:A; 'tab 2'!A:A; etc.], "select....") – TheMrIggs Apr 15 at 21:28
  • it (should) recalculate on a daily basis with today() inside, or by manually crippling the formula and then re-correcting it. – user0 Apr 15 at 21:36
  • Ah okay that's perfect. Did you have any idea how to accomplish the second part of my comment? I've hit a wall with this. – TheMrIggs Apr 15 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.