I'm trying to query multiple workbooks into a single search box-CodePudding

I'm trying to create a search box from several years of data stored in different workbooks in Google Sheet. For some reason, it gave a #Value! Error and I did follow through correct range {} and with its corresponding cell rage B3:Q, please help me on how do I solve #Value! Error.

=QUERY({
  IMPORTRANGE("1uNc8LNlgaw9_ru9_nfpXC7078W80gm2YQdZrtu2m21s","2004-INV-REPORT!B3:Q");
  IMPORTRANGE("1_MiWf4ivI9h1sgJcCDxsZmh51AQcdlvFFRdjBRlsJHQ","2006-INV-REPORT!B3:Q")
},
"SELECT * WHERE Col2 CONTAINS '"&C2&"' or Col3 CONTAINS '"&C2&"'",0)

CodePudding user response：

first, run each importrange separately to allow access. then try:

=QUERY({
 IMPORTRANGE("1uNc8LNlgaw9_ru9_nfpXC7078W80gm2YQdZrtu2m21s"; "2004-INV-REPORT!B3:Q");
 IMPORTRANGE("1_MiWf4ivI9h1sgJcCDxsZmh51AQcdlvFFRdjBRlsJHQ"; "2006-INV-REPORT!B3:Q")};
 "where Col2 contains '"&C2&"' 
     or Col3 contains '"&C2&"'"; 0)

assuming C2 is not numeric value

CodePudding user response：

I added IFERROR per IMPORTRANGE, it solved the #VALUE! Error.

=QUERY({
  IFERROR(IMPORTRANGE("1uNc8LNlgaw9_ru9_nfpXC7078W80gm2YQdZrtu2m21s","2004-INV-REPORT!B3:Q"),{"","","","","","","","","","","","","","","",""});
  IFERROR(IMPORTRANGE("1_MiWf4ivI9h1sgJcCDxsZmh51AQcdlvFFRdjBRlsJHQ","2006-INV-REPORT!B3:Q"),{"","","","","","","","","","","","","","","",""});
  IFERROR(IMPORTRANGE("1qGRukbtB9m5gOoi-Ne1vR-h_F9-QiXUJZo7QLguTAIY","2007-INV-REPORT!B3:Q"),{"","","","","","","","","","","","","","","",""});
  IFERROR(IMPORTRANGE("1nxMhsz_Op6nfeocOEQSbCJ9nMn6npM6JpLpCQk8zshg","2008-INV-REPORT!B3:Q"),{"","","","","","","","","","","","","","","",""});
  IFERROR(IMPORTRANGE("1KFyG5A6Msl6zV0S3OVXyZQhb_D3Ui2Sx59UN54mQaiE","2009-INV-REPORT!B3:Q"),{"","","","","","","","","","","","","","","",""});
...
...