Wednesday, January 29, 2014

yogi_Pull Data From 'Form Responses 1' For Staff Of CEO (cell 5) With CEO_Staff Data In Sheet '0' Cells H12 to I

                                         Google Spreadsheet   Post  #1504
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
post by Meredith Poynter (https://productforums.google.com/forum/#!mydiscussions/docs/7AYLl-Pk5nc)
How to lookup (using Index Small If Row) with multiple values, returning for all rows
I am working with this formula:
=ARRAYFORMULA(IFERROR(INDEX('Form Responses 1'!$A$2:$M;SMALL(IF('Form Responses 1'!$D$2:$D=$C$5;ROW('Form Responses 1'!$A$2:$M)-1);ROW(A1)),11),""))

$C$5 is a person's name on the spreadsheet.This formula looks that person up in 'Form Responses1'!D, as how ever many times it appears and returns the info (in the 11th column) from those rows. It works great where I need it to, but now, I'd like to have the formula look up multiple people's name and return all rows for each person. I've tried modifying the underlined section of the formula to have multiple values, but have had no success.

Please help. I haven't been able to find a solution upon searching. THANK YOU!
---
Here is the skeleton of my spreadsheet. https://docs.google.com/spreadsheet/ccc?key=0ApOYbVKFbSMNdHVKMVIwb1lROEFORkdjd2Q4YXNpVFE&usp=sharing


I'm looking for formulas to go in "Customer Report" Rows 21 and down. I want them to pull the information from "Form Response 1" for the CEO and any Staff that works for that CEO. Currently, the formula only pulls data for the CEO. 

As the current selection of "John B" for CEO on the Customer Report" it should show the information in Row 21 as it currently shows, and then also show information from row 4 in "Form Response 1" as Ashley P works for John B. 

On the "0" tab, I created a table that will always populate a column with the CEO selected at the top of the "Customer Report" and the CEO's staff, to help with the process. My thinking was to have the formulas in the "Customer Report " rows 21 and down to use '0'!J14:J21 as the values to lookup but couldn't figure out how to do it, if that is even the right thinking to do it. I can not have the form response have the CEO and staff in different columns - that would be too easy!


Thanks for your help!
-------------------------------------------------------------------------------------------------------------------------

No comments:

Post a Comment