Use query to combine data from ever-changing tab list

Question

I am building a Sheet that will be taking imported data from other tabs and combining it in a summary sheet, for me to use elsewhere. The problem is, the tabs will be imported monthly, so there will be dozens of them very quickly, and I don't want to edit the query function every month. They will all have the same naming format, but I'm wondering if anyone has an idea about how to formulate the query with variables for the sheet name that I can pull from another cell. For example, I could import the tab names into another row, and have the query pull the sheet names from that row, but I am not familiar with how to do this. Here is an example of the query formula I'm using in a similar sheet:

=QUERY({'2019 TEAM SALES'!A2:ZY; '2020 TEAM SALES'!A2:ZY}, "select * Where Col1 is not null",1)

With this one, updated once a year isn't a big deal. But this new sheet will be monthly, so it will be unwieldy in just a few months. Is there a way using query to take the sheet names via cell info? Like if in row 1, I concatanate the sheet names with the range, so that A1 contains "'2019 TEAM SALES'!A2:ZY" and the next in B1 and so on, and then the query formula is:

=QUERY(A1:ZZ1, "select * Where Col1 is not null",1)

This way, it will always pull in all the current tabs necessary to combine. Incidentally, I'm open to using other formulas or even a script to get this done, as it's the result I'm after, not the method.

I have seen a lot of answers similar to what's here, but this still forces extremely long formulas that I don't think are very efficient, given that this sheet will eventually have 50 or more tabs in it. Thanks for any insights!

Answer 1

to generate list of sheets from start date to today's date +3 months in advance:

=ARRAYFORMULA(UNIQUE(TEXT(ROW(INDIRECT(
 DATEVALUE("2020-4-1")&":"&
 DATEVALUE(TODAY()+90))), "yyyy mmmm")))

---

converting it into ranges for query:

=ARRAYFORMULA("INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(
 DATEVALUE("2020-4-1")&":"&
 DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C"")")

---

to avoid possible errors for sheets that do not exist yet:

=ARRAYFORMULA("IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(
 DATEVALUE("2020-4-1")&":"&
 DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&
 TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")

---

next we join it to create one single reference for query:

=ARRAYFORMULA("{"&TEXTJOIN("; ", 1, 
 "IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(
 DATEVALUE("2020-4-1")&":"&
 DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&
 TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")&"}")

---

then we can build our query:

={""; ARRAYFORMULA("=QUERY({"&TEXTJOIN("; ", 1, 
 "IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(
 DATEVALUE("2020-4-1")&":"&
 DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&
 TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")
 &"}, ""select Col1,Col2 where Col3 is not null"", 0)")}

---

this way we generated our query formula as a string in G2 so the next step is either manually copy-paste this each month where you need it or you can use script which will auto-copy content of G2 and auto-paste it where you need it on any change so you dont need to touch it anymore

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');  // sheet name
var src = sheet.getRange("G2");                                   // cell which holds the formula
var str = src.getValue();  
var cell = sheet.getRange("G10");                                 // cell where I want the results
cell.setFormula(str);              
}

spreadsheet demo

Answer 2

Solution

In order to use cell values (your variables) in your formula in order to automate the whole thing you can use the formula indirect which lets you use cell values in formulas. Therefore, you would end up with something like this:

=QUERY({INDIRECT(A1);INDIRECT(A1)}, "select * Where Col1 is not null",1)

where A1 is the cell containing 2019 TEAM SALES!A2:Y2

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)