How to importrange row data with query based on a formula?

Question

I'm trying to import specific row data from different sheets using the IMPORTRANGE and QUERY functions.

The main issue I can't resolve is I need criteria for each sheet.

I have a summary sheet. I'd like to change the date in Cell B1 and have each row A5:A fill with the data from the appropriate sheet.

Summary sheet:

Summary

Sheet2:

Sheet2

Sheet3:

Sheet3

I've tried using the transpose function to fit the query format. But the query is then only based on one cell.

So, for example, the rows in Sheet start at 201930, but show in the 201928 column in the summary.

Here is an example sheet.

player0 player0 · Accepted Answer · 2019-07-09T06:17:59

you almost had it. delete everything you have in B2:J on Sheet1

paste this in B2 cell:

=ARRAYFORMULA(IF(B1:1<>"", YEAR(B1:1)&WEEKNUM(B1:1), ))

paste this in B3 cell:

=ARRAYFORMULA(IF(B1:1<>"", 
 TRANSPOSE(MMULT(TRANSPOSE(IF(B5:Z="", 0, B5:Z)), ROW(B5:B)^0)) ,))

paste this in B5 cell and drag down:

=ARRAYFORMULA(IFERROR(HLOOKUP(B$1:$1, 
 {INDIRECT(A5&"!D10:10"); INDIRECT(A5&"!D9:9")}, 2, 0)))

actual IMPORTRANGE formula would be:

=ARRAYFORMULA(IFERROR(HLOOKUP(B$1:$1, 
 {IMPORTRANGE("ID_OR_URL", A5&"!D10:10"); 
  IMPORTRANGE("ID_OR_URL", A5&"!D9:9")}, 2, 0)))