How to query multiple sheets and merge the data?

google-sheets google-sheets-query
Kenci picture Kenci · Aug 8, 2012 · Viewed 22.2k times · Source

I have 15 sheets that use the same template, for 15 different users. I would like to make a query on all the sheets, and display the result in a separate sheet:

=QUERY(Mona!A3:U300; "select A, D where C != ''")

Where Mona is the name of 1 of the 15 sheets.

So what I need is to make this QUERY on all the sheets and show the result. The columns (A,D) should only occur once.

How can I accomplish this?

Answer

Max Makhrov picture Max Makhrov · Feb 18, 2016

Let me else suggest using {} to collect all data:

Sample formula for 3 sheets:

=QUERY({'1'!A1:D13;'2'!A2:D13;'3'!A2:D13},"select Col1, Col2")

Here we use header only once in worksheet #1: range '1'!A1:D13 contains header, range '2'!A2:D13 and others don't. Also we replace A, B, C notation with Col1, Col2, Col3...

Look at example workbook, may be someone'll find it hefpful.

Related questions

QUERY syntax using cell reference

I'm having trouble figuring out a fairly simple QUERY statement in Google Spreadsheets. I'm trying to use a cell reference instead of static values and I'm running into trouble. Below it the code I'm using, but I keep getting a "…

Read More
Google SpreadSheet Query: Can I remove column header?

I'm doing this query at my google spreadsheet: =QUERY(H4:L35;"select sum(L) where H='First Week'"; -1) But it returns a little table with "sum" as header and result below it. What I want is just the result! …

Read More
Google Sheets: How to replace text in column header?

I have a query like this =QUERY(B2:C9; "select (C * 100 / B) - 100") in my Google Sheets. What is displayed as a column header is: difference(quotient(product(100.0()))100.0()). I want to put a human readable description there instead. How can …

Read More