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 '19 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)

| improve this answer | |
  • 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 '19 at 21:08
  • @user205206 yeah, try now 2nd one – user0 Jan 2 '19 at 21:13
  • Amazing! Thanks! We're getting closer; I've got my people, but it's still importing everyone. – user205206 Jan 2 '19 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 '19 at 21:28
  • @user205206 I've updated my answer – user0 Jan 2 '19 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)
| improve this answer | |

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