Friday, May 10, 2013

yogi_Extract Comment Entries From Sheet1 For Specified Columns (as noted in column G)


                                          Google Spreadsheet   Post  #1184
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 10, 2013
user Matt Bateman (http://productforums.google.com/forum/?zx=gt4fhhtub7z3#!category-topic/docs/spreadsheets/3M-cCZ9-2Lc)
ArrayFormula or other Formula Needed to return text contents of a given cell

Hello Guru’s and thanks in advance for your help!!
I am trying to find a formula that returns the text from a particular cell that also uses a datevalue in the formula. We built a Google form for monitoring agents and the manager wants to be able to have a sheet that populates the response into a printable sheet.
So I created a sheet that has all the questions in a table and then used Array formulas with a date value
=ArrayFormula(SUM((('Sheet1'!C2:C="Yes")*('Sheet1'!B2:B=A3)*(DATEVALUE('Sheet1'!AW2:AW)>=B3)))) and then created a drop down with the agent names using data validation so you can select the agent and date and have it populate the responses and give a total “Agent Score”. The last step and problem I am running into is that there are also comment sections for each part of the form (In Sheet1 F, N, R, X, AA and AG)
So on the same table I included comment boxes for each section and I was hoping I could just use an Array Formula with the same logic so that when we select the name and date that it would also return the text contained in the corresponding column ('Sheet1'!F2:F)*('Sheet1'!B2:B=A3)*(DATEVALUE('Sheet1'!AW2:AW)=B3)))) but so far every variance I have tried comes back with an error such as “Not a Number” or “Wrong number of arguments”
I have been trying to research this but have not come across a formula that just returns the text contents of a given cell. I included a snapshot of what it looks like and can share a copy of the doc if that helps.
Any thoughts or help would be greatly appreciated
---
Here is a link to a copy of what I am working on. If you look on the sheet named Printable QA Form I think it will be fairly self explanatory what I am trying to accomplish. All of the other formulas are functioning as they should. The last step is to populate the Comments boxes with the comment responses from sheet1.
On Sheet1 the comments are in F, N, R, X, AA, AG –Right now when you are in the Printable QA Form and select the name from the drop down in A3 and Date from B3 (for example Matt / 04/30) it pulls all the responses into their corresponding fields and I just need to do the same with the comments and this sucker will be done J
Thanks Again everyone  
-----------------------------------------------------------------------------------------------------------------


let us have a look at the following solution

3 comments:

  1. So first off thank you so much for your help on this. I had originally thought that a query may be the best answer. The only issue I am having now is for the Comments it does not appear to be working with the date range. It pulls the 1st set of comments only for the selected agent. Is there an easy way I can add a date value into the formula so it also looks for the comments by date?

    ReplyDelete
  2. Hi Matt:

    That should be doable ...
    Via the question you posted in Google Docs User Forum, show me your expected result along with needed logic and explanation as to why that is the correct result ... and then let us take it from there.

    Make It A Great One
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  3. This worked perfectly!
    Thank you so much for all your help

    ReplyDelete