Google Spreadsheet Query for Room Reporting Project

Question:

Hi there,

I need help with the following sheet:

The Form Responses is a type of scoring system for peoples rooms. The rooms are scored according to Good, Ok or Bad.
On the Ad Hoc Reporting tab I want to able to filter those results according to Person, Zone, and between certain dates, but I still want the whole line -
Timestamp Person Zone  [Bed]  [Closet]  [Clothing]  [Hygene]  [Dishes]     -
and the data to pull through.
From there I can then create reports based on each individuals results.(see sheet2)
If someone can help me with a formula to do just that I will be #$%^&* greatful , cause I have tried and tried but no luck!
Thanks,


Solution:

Now put the following formula in Cell A7:

=query('Form Responses'!A:H;"select * where B = '"&B5&"' and C = '" &D5& "'")

It will provide you the following results:

Use the following formula for "ALL" case (either in Cell B5 or in Cell D5):

=if(and(B5="All";D5="All");query('Form Responses'!A:H;"select *");if(B5="All";query('Form Responses'!A:H;"select * where C = '" &D5& "'");if(D5="All";query('Form Responses'!A:H;"select * where B = '"&B5&"'");query('Form Responses'!A:H;"select * where B = '"&B5&"' and C = '" &D5& "'"))))


And use the following formula for also comparing the date which are in Cell F5 and Cell F6:

=if(and(B5="All";D5="All");query('Form Responses'!A:H;"select * where toDate(A) >= date '" & text(F5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(F6,"yyyy-MM-dd") &"'");if(B5="All";query('Form Responses'!A:H;"select * where C = '" &D5& "' and toDate(A) >= date '" & text(F5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(F6,"yyyy-MM-dd") &"'");if(D5="All";query('Form Responses'!A:H;"select * where B = '"&B5&"' and toDate(A) >= date '" & text(F5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(F6,"yyyy-MM-dd") &"'");query('Form Responses'!A:H;"select * where B = '"&B5&"' and C = '" &D5& "' and toDate(A) >= date '" & text(F5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(F6,"yyyy-MM-dd") &"'"))))


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive