## Wednesday, June 19, 2013

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:

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,

1. He Kishan,

Thanks for the quick response!

Is it possible to do the following - in the dropdown list on B5 and D5 there is a "All" option.

When "All" is selected in either that it will pull all the lines through?

I also need to be able to search between certain dates as specified in F5&F6?

Yet again thanks for your help!!!!!! You are a blessing!

1. Hi,

Use the following formula:

=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& "'"))))

I hope that helps you..
:)

2. Hi Kishan,

Awesomeness, it works beautifully!

I've tried working this part into the formula and A > date """&text(F5,"yyyy-MM-dd")& and and A < date """&text(F6,"yyyy-MM-dd")& so that I can search between two specified dates. But I keep getting a parse error.

What do you recommend we do there?

3. Hi Ruan,

I have updated this post so read it again for the solution.

Thanks,
Kishan.

4. Kishan You are a CHAMPION! Thanks, it works 100%!

I also used that script for another reporting tool, (had to do some alterations...but figured it out...I don't really know how this stuff works!) and the query works just fine.

I also came apon this script you wrote -