I have created two spreadsheets.

This one is my source data: https://docs.google.com/spreadsheet_A

And I would like this one to just be the people who are currently here (so Column D < today and Column E > today): https://docs.google.com/spreadsheets_B

I keep tinkering with the formula for the date, but can't get it right. Most recent error I've gotten is

Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: A.

Any thoughts?

  • Welcome. What is the formula that you tried? – Rubén Jan 2 at 21:39

use Col1,Col2,Col3,Col4 instead of A,B,C,D when you QUERY the IMPORTRANGE

=QUERY(IMPORTRANGE("16ATkORZt0_vEUdyKMk1trWymhfJ5-aJPlMdRT0-yM9Q", "A1:E"), 
 "select Col1,Col2,Col3,Col4 where Col4 >= '"&TEXT(DATE(2018,1,2),"yyyy-MM-dd")&"'", 1)

=QUERY(IMPORTRANGE("16ATkORZt0_vEUdyKMk1trWymhfJ5-aJPlMdRT0-yM9Q", "A2:D"),
 "select Col4 where Col4 >= '"&TEXT(DATE(2019,1,2),"yyyy-MM-dd")&"'")

=QUERY(IMPORTRANGE("16ATkORZt0_vEUdyKMk1trWymhfJ5-aJPlMdRT0-yM9Q", "A1:E"), 
"select Col1, Col2, Col3, Col4, Col5 where Col5 <= '" &TEXT(E2, "yyyy-MM-dd")& 
                                    "' AND Col4 >= '" &TEXT(D2, "yyyy-MM-dd")& "'")

UPDATE - final solution:

=QUERY(IMPORTRANGE("16ATkORZt0_vEUdyKMk1trWymhfJ5-aJPlMdRT0-yM9Q", "A1:E"), 
 "select Col1,Col2,Col3,Col4 where Col5 >= '"&TEXT(DATE(2019,1,2),"yyyy-MM-dd")&"'
                               and Col4 <  '"&TEXT(DATE(2019,1,2),"yyyy-MM-dd")&"'", 1)

  • Thanks so much for replying! That first formula keeps giving me the date 2018-01-10. The second formula just gives me my header rows and if I edit the row number manually in subsequent rows, it imports everyone, not just our current folks. Thanks! – user205206 Jan 2 at 21:08
  • @user205206 yeah, try now 2nd one – user0 Jan 2 at 21:13
  • Amazing! Thanks! We're getting closer; I've got my people, but it's still importing everyone. – user205206 Jan 2 at 21:18
  • There's a Jan 10 19 arrival and a Dec 28 18 departure in there, so those two shouldn't import. – user205206 Jan 2 at 21:28
  • @user205206 I've updated my answer – user0 Jan 2 at 22:08

Resolved by @user0 with:

=QUERY(IMPORTRANGE("16ATkORZt0_vEUdyKMk1trWymhfJ5-aJPlMdRT0-yM9Q", "A1:E"), 
 "select Col1,Col2,Col3 where Col5 >= '"&TEXT(DATE(2019,1,2),"yyyy-MM-dd")&"'
                               and Col4 <  '"&TEXT(DATE(2019,1,2),"yyyy-MM-dd")&"'", 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.