Sunday, May 25, 2014

yogi_Pull In 'Single Case' Child Stats By Querying 'Raw Data' By Parent First Name

                                         Google Spreadsheet   Post  #1649
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   May-26-2014
post by Bruce Gale: (!mydiscussions/docs/2nQ9bYqQlRs)
Dynamically changing row numbers in a formula
I have created a short (2 min) video that indicates what I am trying accomplish.  Credits to this forum, because my original request was going to be how to use the "OR" function in an array, but I resolved that.  On to business....

I wish to point to a single row as part of a formula.  Click below to view the video (hopefully it has processed by the time this is viewed, otherwise requires a download to view):

Thanks for any assistance you can provide.

Okay thanks, I have removed the original data.  This is what I am trying to accomplish.

Here is the sample spreadsheet

I have Raw Data containing multiple children who have been rated, e.g., Susan" in row 3 below

I want to be able to extract data from a single case as below, as appears in the spreadsheet Single Case

=TRANSPOSE('Raw Data'!A15:I15)

but switch from one row to another without having to change all of the numbers (e.g., row 15).  

Somehow I need a variable to point to the correct row to make the data dynamically change from one case to another.  Then the filter in subsequent spreadsheets will all reflect that single rater/case.  

=FILTER('Single Case'!A2:B11,('Single Case'!B2:B11 = "5-Significant Improvement")+('Single Case'!B2:B11 = "4-Moderate Improvement"))

E.g., switch from row 3 in Raw Data (Susan) to row 5 (Martha) to get those results.

Please let me know if I need to clarify further.  And thanks for any help you can provide.