0

Currently using:

=IFERROR({QUERY(PRI!A4:T,D1,1);QUERY(NON!A5:T,D1,0)},"Nada")

This is used to grab information from two spreadsheets with the same number of columns, with PRI grabbing data with headers and NON grabbing just data.

D1: "select A, B, C, D, E, I, J, K, L, M, N, O, P, Q, R, S, T WHERE A >= date'" & TEXT(B2,"yyyy-mm-dd") & "' and A <= date '" & TEXT(C2,"yyyy-mm-dd") & "'"

This is the reference for the query to look up the start and end date based on two specific cells.

The query using D1 will filter if both spreadsheets have an entry in a specific date range.

EX: PRI and NON both have an Apr7 entry which the query shows both if START is Apr7 and END is Apr13.

But the issue is if only one of the two spreadsheets has an entry then the query shows nothing if the START and END criteria are still correct despite their being an entry between the date criteria.

Is there a way for the query to recognize both spreadsheets at the same time, instead of a "only run if BOTH PRI and NON have an entry" behavior? I definitely feel the query itself is the problem but I could not find any format that works alongside D1.

  • I added a fix for header row. 17x "", are fake columns to avoid errors. if the answer helped you pls accept it and upvote it. – user0 Mar 24 at 13:49
0
  • try it like this:

=IFERROR(
 {IFERROR(QUERY(PRI!A4:T, D1, 1), {"","","","","","","","","","","","","","","","",""});
  IFERROR(QUERY(NON!A5:T, D1, 0), {"","","","","","","","","","","","","","","","",""})}, 
 "Nada")

  • and with skipping empty query:

=IFERROR(QUERY(
 {IFERROR(QUERY(PRI!A4:T, D1, 1), {"","","","","","","","","","","","","","","","",""});
  IFERROR(QUERY(NON!A5:T, D1, 0), {"","","","","","","","","","","","","","","","",""})}, 
 "where Col1 is not null", 0), 
 "Nada")

  • for a header row fix:

=IFERROR(QUERY(
 {IFERROR(QUERY(PRI!A4:T, D1, 1), 
          QUERY(PRI!A4:T4, "select A,B,C,D,E,I,J,K,L,M,N,O,P,Q,R,S,T", 0)));
  IFERROR(QUERY(NON!A5:T, D1, 0), {"","","","","","","","","","","","","","","","",""})}, 
 "where Col1 is not null", 0), 
 "Nada")

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.