Creating a query that will filter based on multiple criteria from dropdown menus in Google Sheets

1
votes

I am trying to create a dashboard that will filter data based on multiple criteria.

Here is a copy of the spreadsheet: https://docs.google.com/spreadsheets/d/1sJcsu0VpnLBi7wh4l-NEFdYfkxWgC6vpX8AeN9u-KlQ/edit?usp=sharing

I am having a hard time getting the query to work as I want.

I have created data validation cells for several search criteria (Name, reporter, incident type, location, start date, end date).

There are four columns for student names, based on the way the form is set up. I am able to get it to filter based on the student name using this formula: =QUERY('Form Data'!$A$2:M,"Select * where B = '"&E1&"' OR C = '"&E1&"' OR D = '"&E1&"' OR E = '"&E1&"'",0)

When I try to add other criteria, the formula breaks down. I've tried:

Select * where B = '"&E1&"' OR C = '"&E1&"' OR D = '"&E1&"' OR E = '"&E1&"' AND D >= Date '"&B11&"' AND A <= Date '"&B12&"' to try and filter date, but I'm getting an error. It says the date format isn't correct, but it's formatted yyyy/MM/dd as it says it should be.

I also tried: =QUERY('Form Data'!$A$2:M,"Select * where B = '"&E1&"' OR C = '"&E1&"' OR D = '"&E1&"' OR E = '"&E1&"'" &if(B5="All",,"'"&B5&"'"),0); this works when "All" is selected, but not when I pick another name from the drop down list.

Any help would be enormously appreciated

1
google-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

1 Answers

0
votes

try:

=QUERY('Form Data'!A2:M, 
 "where (B = '"&E1&"' 
     or  C = '"&E1&"' 
     or  D = '"&E1&"' 
     or  E = '"&E1&"') 
    and  A >= date '"&TEXT(B11, "yyyy-mm-dd")&"'
    and  A <= date '"&TEXT(B12, "yyyy-mm-dd")&"'", 0)

0