yogi_Pull Name From A2:A51 For Specified Debate Type In E2:E51

Google Spreadsheet   Post  #2179

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-09-2017

question by Jon Baney:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/LkhrFcDZ4_Y;context-place=forum/docshttps://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/8QCT_s7s_pg;context-place=mydiscussions

IF or Lookup function (If false, find next true cell)

Okay, here it goes. This is as much as I can get you, it isn't the actual sheet that I will be using, but a copy (I'm a teacher and for one, I cannot just publish student info like emails to the web, and for two...this is something that I'm creating now and won't have real data for until schools starts, so the data is fake, but it is realistic to what I need):

https://docs.google.com/spreadsheets/d/1ZEINvy0xnZkMAbX_xPWHxne6BSIhuI__hQbCOVvTudU/edit?usp=sharing

I have a line inserted between row 52 and 53 which divides the "tables". The lower table is based on responses in the top table (I am trying to get lists of student names who are competing in each event). I typed into each cell on row 54 what the function for that column from that point down should be looking for. To demonstrate what the data should look like as a finished product, I manually input what the table should look like after the formula is applied to each column. This "desired data" starts on the next row, 55, and continues down until in each column until there is no more "true" responses to the function.

I think I provided you with what you requested. As far as the logic goes for why I need this, I need to be able to be able to view all of the input from the top table as-is (it is actually a query pulled from another sheet that will be linked to a google form and will update as students "sign up" for each tournament) because I need to be able to view that data exclusive to that student for various reasons. I need the bottom table because I need to be able to easily see how many students are competing in all of the events without having to a) reorder the sheet or b) pull all of the responses to show and have 50+ responses under each event some of which says "student name from column A" and the rest saying "false". Starting on row 100 I have an example of what I WAS able to get, but you will see why that is undesirable...I want each column to only show the "true" responses--though, in collumn 101D-101L, I am not sure how to make those formulas search for a phrase within text and kick back a true statement if true, which is what I want (and is my second question listed in my original post). Example desired formula: Give me the value for column A any time column F contains "informative speaking":

Cell F="Informative Speaking, Original Oratory" (THE FORMULA IS TRUE!)

Cell F="Informative Speaking" (THE FORMULA IS TRUE!)

Cell F="Original Oratory, International Extemp" (THE FORMULA IS FALSE; this cell would show the next true statement.)

Cell F="International Extemp, Informative Speaking" (THE FORMULA IS TRUE; this would actually appear in the cell above because it was false)

I hope I am explaining this all correct and that it can all be done. 

Thanks for your help!