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

---

https://docs.google.com/spreadsheet/ccc?key=0AsO6K36Jt1T7dEZjbTE5cnlfTlhmSUtqNlJnaFFGeGc&usp=sharing

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