importrange using conditions, NO_COLUMN error-CodePudding

I have a spreadsheet that has information about students on it that I want to share to another sheet based on a date. Basically what I have is a discipline referral system to track information for myself, but when I indicate that a student is to serve a detention I have to manually add that to another sheet that is then tracked by the teacher that monitors those detentions. What I would like is for when I indicate a detention on my sheet that it automatically populates to the other teacher's sheet based on a date.

Referral sheet information: Column E has student names, Column U has date of detention

Teacher's monitor sheet: the date for each detention is listed in AG1 for example.

I want the query to find all the dates in Column U of my sheet, that match the date in AG1 of the teachers, and place the student names in the column under the date in AG1.

This is what I have created so far but keep getting an "unable to parse query string for function QUERY parameter 2: NO_COLUMN:Col21" error.

=query(importrange("https://docs.google.com/spreadsheets/d/1KS0FRuHnFdg4oHd31RfAaEoCwjHvhWwHAgCBA42Sajs/edit?resourcekey#gid=462178060","Form Responses 1!E2:E"),"where Col21='AG1'")

CodePudding user response：

You should include the whole range in which you need to apply the conditions in the query inside the IMPORTRANGE. For example:

=query(importrange("https://docs.google.com/spreadsheets/d/1KS0FRuHnFdg4oHd31RfAaEoCwjHvhWwHAgCBA42Sajs/edit?resourcekey#gid=462178060","Form Responses 1!A2:U"),"Select Col5 where Col21='"&AG1&"'")

Or:

=query(importrange("https://docs.google.com/spreadsheets/d/1KS0FRuHnFdg4oHd31RfAaEoCwjHvhWwHAgCBA42Sajs/edit?resourcekey#gid=462178060","Form Responses 1!E2:U"),"Select Col1 where Col17='"&AG1&"'")

And if you need to compare it to a cell you should close quotes and add the value of the cell '"&AG1&"'"

CodePudding user response：

use this first to allow access:

=IMPORTRANGE("1KS0FRuHnFdg4oHd31RfAaEoCwjHvhWwHAgCBA42Sajs", "Form Responses 1!E2")

then use:

=QUERY(IMPORTRANGE("1KS0FRuHnFdg4oHd31RfAaEoCwjHvhWwHAgCBA42Sajs", "Form Responses 1!E2:E"), 
 "where Col1='AG1'", )