I have a sheet that queries three others in the same document and selects a bunch of rows where the column G contains no. Ideally I would prefer it be a query that does not contain the word yes or Yes. I have tried using all of the following mechanisms:
and no matter which one I pick I run into one of two issues:
Issue number 1 is that part of the query fails because only one of the values is present. Ie there is a Yes but not a yes, which results in the output of #VALUE. This is problematic because I am trying to allow for both scenarios since I cannot control what the end user of this will input into the sheet.
or Issue number 2 which is far worse. When I specifically say select all where G contains no one of the rows that displays contains a yes. Note that this also occurs with some of the other mechanisms as well but I would think specifically saying no should prevent this and does not.
The full query I am using is:
=ARRAYFORMULA({QUERY(Sheet1!A2:I500, "Select A, B, C, D, E, F, G, H, I where G contains 'no'");(QUERY(Sheet2!A2:I500, "Select A, B, C, D, E, F, G, H, I where G contains 'no'"));(QUERY(Sheet3!A2:I500, "Select A, B, C, D, E, F, G, H, I where G contains 'no'"))})
What am I doing wrong here?
The problem is the query range. It starts a Row2 which contains data rather than labels. By default the first row of a query is treated as heading for the output (so selection criteria are not applied to it). Hopefully a fix is just to adjust the query to exclude headings. Please try:
=QUERY(Sheet1!A2:I500,"Select * where G = 'no'", 0)