New answers tagged google-sheets-query
0
The default values for checkboxes are TRUE/FALSE. You could use several IF functions, the & operator and REGEXREPLACE to create the SQL argument for QUERY
Let say that A1 to A5 have the checkboxes, the the resulting formula will look like the following one:
=QUERY(something,"Select "®EXREPLACE(IF(A1,"Col1, ",)&IF(A2,"Col31, ",)&IF(A3,"...
1
We usually don't have to conceptualize it this way, but in the "conditions" part of FILTER, we're actually setting up an array of TRUE or FALSE results to control whether each input row makes it through the filter. Perhaps take advantage of that and negate the Booleans outside the REGEXMATCH?
=FILTER(Planning!G$5:G$37; NOT(REGEXMATCH(Planning!G$5:G$37; "a"))...
1
The main problem is that sheetnames function returns a string while the QUERY's first argument should be a range reference or an array of values.
Another problem, is that Google Sheets errors aren't very clear, but regarding this we can only give feedback to Google. For this use Google Feedback.
The solution is to replace your sheetnames custom function ...
0
Try a formula like the following:
=FILTER(D3:N,(B3:B=S$2)+(C3:C=S$2))
Since FILTER conditions are treated as ANDs, using in your two conditions directly would only let rows through if B3:B AND C3:C matched S2. Instead I use a plus sign to add TRUEs and FALSEs since that works as the Boolean OR we need.
Here's the above formula showing matching rows ...
0
You're almost there already. First, you should probably match your filter input 'range' size and 'condition' size:
FILTER("Sheet1"!A1:E1000,"Sheet1"!E1:E1000="A1")
Then we have to somehow extract the letter part of the code in column E. If it is always that simple, I'd use LEFT to grab the single leftmost character:
FILTER("Sheet1"!A1:E1000,LEFT("Sheet1"!...
0
Using the script solution, but sort on more than one column
I wanted to sort by a dropdown menu column and then by date.
To do so, modify the "range.sort" line of either of Cristian or geekspotz's code snippets like so:// Sorts descending by edited column, then ascending by column 1
// Note the use of an array
range.sort([{column: columnToSortBy, ...
0
Use FILTER or QUERY
Example using FILTER
=FILTER('Sheet 1'!A:A,'Sheet 1'!B:B)
0
We finally came up with an eloquent solution using the ARRAY_CONSTRAIN, SORT, QUERY functions.
This is the final formula, small, flexible (both variables out of the formula), row expandable, robust (works with empty cells):
=ARRAY_CONSTRAIN(SORT(
QUERY(A2:E, "select * where D >= date '" & text(J21, "yyyy-mm-dd") & "'"),
4,FALSE,5,),M21,5)...
Top 50 recent answers are included
