I’m going to try to explain my problem in English (I’m French).. sorry for the mistakes.
I’m trying to fill a table with two queries sort by date.
I have a first sheet with some datas (DATE+NAME+DESCRIPTION+PRICE)
I have a second sheet with some other datas (DATE+NAME+DESCRIPTION+PRICE)
I would just combine all this informations in another sheet sort by date
But if a query (for example, no datas in the first sheet) is empty, I have a #value! error.
=SORT({QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1z9luLttE6HX5hzXNxCeK9fAZRMDqqKvhwSG1_lnhT-o/edit?gid=0#gid=0";"Journal!$A$9:$F$200");"select Col1,Col2,Col5,Col4 where Col3='Versement Charges' and Col6='" & H1&"'";0);QUERY(Journal!$A$7:$F$213;"select Col1,Col2,Col4,Col5 where Col3='" & H1&"'";0)};1;VRAI)
I tried to change the formula with a “iferror” function… like this :
=ARRAYFORMULA(SIERREUR({SIERREUR(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1z9luLttE6HX5hzXNxCeK9fAZRMDqqKvhwSG1_lnhT-o/edit?gid=0#gid=0";"Journal!$A$9:$F$200");"select Col1,Col2,Col5,Col4 where Col3='Versement Charges' and Col6='" & H1&"'";0);"");SIERREUR(QUERY(Journal!$A$7:$F$213;"select Col1,Col2,Col4,Col5 where Col3='" & H1&"'";0);"")}))
but it responds me an empty cell…
I’m going to die trying to fix it and I don’t understand how to skip empty datas but fill with the second sheet datas. 🙂
thanks !!!!
sebvass is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
You can try using this. I used Let to create a null. I stacked the query responses using vstack, and used iferror to replace an empty query with a null value. As a note, this will still error out if both queries are null because there won’t be an array to sort.
=let(z,tocol(,1),SORT(vstack(iferror(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1z9luLttE6HX5hzXNxCeK9fAZRMDqqKvhwSG1_lnhT-o/edit?gid=0#gid=0","Journal!$A$9:$F$200"),"select Col1,Col2,Col5,Col4 where Col3='Versement Charges' and Col6='" & H1&"'",0),z),iferror(QUERY(Journal!$A$7:$F$213,"select Col1,Col2,Col4,Col5 where Col3='" & H1&"'",0),z)),1,1))
1