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 


7 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
  3. Explains what to look for in the breed. Is this breed good for kids and retired people? Questions and answers most people want to know about Poodles. Famous teddy bears

    ReplyDelete