Wednesday, June 19, 2013

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:


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 


6 comments:

  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!

    ReplyDelete
    Replies
    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..
      :)

      Delete
    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?

      Delete
    3. Hi Ruan,

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

      Thanks,
      Kishan.

      Delete
    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 -
      http://igoogledrive.blogspot.in/2012/10/Google-Spreadsheet-Script-to-Copy-Sheet-to-multiple-Spreadsheets-at-a-time.html

      And used it with great success!

      Thanks again- you are a LEGEND!

      Delete
  2. I have the same style spreadsheet (it will be for duties assigned to the same people) and I reckon I will be able to use the same formula for it.

    ReplyDelete