consolidate specific row values from different she

问题:

I'm looking for a way to consolidate specific row values from multiple spreadsheets when certain validation are met.

Scenario: I currently have Sheet1, Sheet2, Sheet3 what I want is to consolidate the data from sheet2 and sheet3 into sheet1 when the validation is met.

Sheet1:

Sheet2:

Sheet3:

Based on the example above, I used this function in sheet1 cell A2

=iferror(query(Sheet2!A2:E, "where E = 'New Player' ",0),"")

so what happens is it automatically adds the row values from Sheet2 if Column E "Type" is "New Player" (validation).

What I want is I would also like to add sheet3 row data if its type is "New Player".

maybe there's a way to nest query function? or can this be done using script? I found some scripts that is able to copy rows but without the validation and not from multiple sheets.

回答1:

Assuming you are querying within the same spreadsheet, something like this should work

=iferror(query({Sheet2!A2:E; Sheet3!A2:E}, "where Col5 = 'New Player' ",0),"")

EDIT: And if you want to pick which columns to return (example A, B, C but not the rest)

=iferror(query({Sheet2!A2:E; Sheet3!A2:E}, "Select Col1, Col2, Col3 where Col5 = 'New Player' ",0),"")