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?
This doesn't solve the merge part of your problem, but an efficient way of querying all the sheets is:
=QUERY(Indirect(A1); "select A, D where C != ''")
where cell A1 has the text value Mona!A3:U300
With this method you can list the different sheet names in cells and have a lookup for each one.
It might be possible to use an ArrayFormula to combine the queries into one formula, though I'm not sure how well query would work for this - using either Sumproduct or Filter to look up values using multiple criteria might work better.