Use Google Sheets ARRAYFORMULA to create QUERY input

Question

I have a QUERY that looks up values from various separate sheets, this is its input:

=QUERY( {
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col2,  Col4,  Col5  where Col1 IS NOT NULL and Col4  IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col6,  Col8,  Col9  where Col1 IS NOT NULL and Col8  IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col10, Col12, Col13 where Col1 IS NOT NULL and Col12 IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col14, Col16, Col17 where Col1 IS NOT NULL and Col16 IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col18, Col20, Col21 where Col1 IS NOT NULL and Col20 IS NOT NULL", 0), { "","","","","" } )
}, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1")

The {} gets repetitive and longwinded, and requires manually updating every time I add another week.

Recently I discovered I can generate a list of Week sheet names using this formula:

=ARRAYFORMULA(
    "Week " & 
    {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}
    & "!A2:Z133"
)

and then in another column list only those sheets that actually exist:

=IF(
        ISERROR(CELL("address",INDIRECT($U2))),
        "",
        $U2
)

Now I have a column with values such as Week1!A2:Z133, Week2!A2:Z133, etc. How can I use this column to create the QUERY formula source automatically?

Using this formula gets me the first range referenced but none of the subsequent ones in the column:

={ARRAYFORMULA(INDIRECT(AA:AA) )}

I can use {INDIRECT( ARRAYFORMULA( "Week " & { 1;2;3} & "!A2:Z133" ) )} but it will fail if Week 3 doesn't exist, if I can easily filter out the items that are not valid cell references I think I'll have a solution that avoids needing any columns — Tom J Nowell
– Tom J Nowell, Commented Oct 8, 2021 at 15:16
This is an extremely commonly asked question and the common answer is that you should not keep track of weekly data on different tabs, but rather on ONE tab that just as one additional column to note the week from which the data is being entered. — MattKing
– MattKing, Commented Oct 8, 2021 at 15:28
I know that, but that is not an option here ( this formula is compiling it into a single tab so that I can normalise things ). I have a separate sheet that has a neverending sequence of data that does follow the recommended pattern, but I don't need help with that sheet, it's the weekly sheets. — Tom J Nowell
– Tom J Nowell, Commented Oct 8, 2021 at 15:31
if I take my column of references and try to display them using ={ARRAYFORMULA(INDIRECT( only the first references results are shown — Tom J Nowell
– Tom J Nowell, Commented Oct 8, 2021 at 15:38

Tom J Nowell · Accepted Answer · 2021-10-09 00:08:51Z

2

Here within this sheet is a basic script for combining tabs that start with the word "Week".

function tabCombo(){
    var ss = SpreadsheetApp.getActive();

    //Filters sheets to just the ones that start with "Week"
    var sheets = ss.getSheets().filter(function (e){return e.getName().slice(0,4)=='Week'});

    //combines all tab values into one array and filters out the rows with a certain value in the first column
    var combo = sheets.map(e=>e.getDataRange().getValues()).flat().filter(e=>e[0]!='Header1');

    //writes that new value to a 'Master' tab.
    ss.getRange('Master!A2').offset(0,0,combo.length,combo[0].length).setValues(combo);
}

Take note of the word "Week" which is how it decides which tabs to grab.

Take note of the number 4 (which is how many letters "Week" has)

Take note of the range "Master!A2" which is the top left corner of where the combined data should go.

Take note of the term "Header1" which is how the combined array filters out the header rows from all the tabs.

edited Oct 9, 2021 at 0:08

Tom J Nowell

10k18 gold badges66 silver badges94 bronze badges

answered Oct 8, 2021 at 17:48

MattKing

7,80310 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom J Nowell Over a year ago

I had hoped not to need to resort to JS but it looks like that's the only option, I'll test this out

Collectives™ on Stack Overflow

Use Google Sheets ARRAYFORMULA to create QUERY input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related