yogi_Query Data From A Table For Specific Values In Columns Including The Option of Choosing All Values In Specified Columns

                                          Google Spreadsheet   Post  #1257
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 21, 2013
user Ruan Davel (http://productforums.google.com/forum/?zx=whyulvkvwedi#!category-topic/docs/spreadsheets/Uw5ITuWe53w)

Hey Folks,

Here is the sheet - https://docs.google.com/spreadsheet/ccc?key=0ArcfEX6pWVF6dDVfa1drd1NmTGlnUWJ1N1dBVWFwLUE#gid=1

What I am trying to achieve is that you can search/filter the data coming in from the form according to -

Name

Kitchen

Time

and Between 2 dates.

This is the formula - 

=if(and(B5="All";D5="All";F5="All");query('Form Responses 1'!A:K;"select *

where toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'");if(B5="All";query('Form Responses 1'!A:K;"select * where C = '" &D5& "' and B = '" &F5& "' and toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'");if(D5="All";query('Form Responses 1'!A:K;"select * where B = '" &F5& "' and D = '" &F5& "' and toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'");if(F5="All";query('Form Responses 1'!A:K;"select * where D = '" &B5& "' and C = ‘”$D5$”’ and toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'");query('Form Responses 1'!A:K;"select * where B = '" &F5& "' and C = '" &D5& "' and D = '" &B5& "' and toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'")))))

The problem is that when I filter/search according to a specific name in B5 and D5 & F5 = All then I doesn't give the result.

So obviously in the formula I am using I have missed something....completely....

If someone knows what is needed please let me know.

---

I guess the best way to say what I want to do is this.

The data in B5, D5 and F5 (as selected in the drop down lists) should correspond with the rows of data from where I am getting it from in the form responses sheet.

But when I select "All" in B5, D5 or F5 (or when only one of them) then all the corresponding rows from the dorm responses sheet should come up.

Hope that makes more sense....

-------------------------------------------------------------------------------------------------------------------------------------------------------