Menu
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?
806
On the Data tab, under Tools, click Consolidate. In the Function box, click the function that you want Excel to use to consolidate the data. In each source sheet, select your data, and then click Add. The file path is entered in All references.
Combine and Transform Data To combine all the files with a query, and then launch the Power Query Editor, select Combine > Combine and Transform Data. Combine and Load To display the Sample file dialog box, create a query, and then load to worksheet, select Combine > Combine and Load.
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.
125
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.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
