CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Nick
Nick

Reputation: 11

Using indirect to pull from multiple sheets for a Query

I have a dynamic list of the sheets in a Google workbook. I am hoping to create a query that pulls from these different sheets - although I am often adding and deleting sheets.

Right now my query looks like, and it works great: 

=query({'1.17 invoice'!A25:N;'1.31 invoice'!A25:N;'2.1 invoice'!A25:N;'invoice 2.1 (2)'!A25:N},"SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

I can create a cell A1 that has a value of: '1.17 invoice'!A25:N;'1.31 invoice'!A25:N;'2.1 invoice'!A25:N;'invoice 2.1 (2)'!A25:N

But right now I have to copy and paste the results of A1 into the query formula in order for it to update. When I try to create a formula like: 

=query({indirect(A1)},"SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

I just get an error that 'the results of the indirect are not a valid cell range'. Is there any good way to automate getting the list of sheets into the query function?

Upvotes: 1

Views: 1660

Answers (1)

Tedinoz
Tedinoz

Reputation: 7949

The OP has tried to make the array range more flexible. Original formula: 

=query({'1.17 invoice'!A25:N;
        '1.31 invoice'!A25:N;
         '2.1 invoice'!A25:N;
     'invoice 2.1 (2)'!A25:N},
 "SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

The OP entered the array range in cell A1, and modified the formula:

A1 = '1.17 invoice'!A25:N;'1.31 invoice'!A25:N;'2.1 invoice'!A25:N;'invoice 2.1 (2)'!A25:N

Modified formula: 

=query({indirect(A1)},"SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

This doesn't work because INDIRECT returns a single array value, but A1 consists of several ranges. However, if each of the ranges is declared separately, the query will work.

Layout: 

A1 = '1.17 invoice'!A25:N    
A2 = '1.31 invoice'!A25:N    
A3 = '2.1 invoice'!A25:N    
A4 = 'invoice 2.1 (2)'!A25:N

Formula:

=query({indirect(A1);
        Indirect(A2);
        indirect(A3);
        Indirect(A4)},
 "SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

Credit:
Ruben (https://webapps.stackexchange.com/a/88726/196152)
Karl_S (https://webapps.stackexchange.com/a/104517/196152)

Upvotes: 2

Related Questions