I have the following data:

Sample Data

and would like to create a separate sheet that would Identify the First name, Last name and the header of columns E:N if they have a value of 1.

I would like to have the separate sheet include a dropdown list of names in column A1 to populate the data.

1 Answer 1


UNTESTED (what, with that much data and in a barely legible image ?!)

Copy the entire populated part of your spreadsheet below existing data (say to A100) and fill the central area (starting E101) with:


Count row contents with:


in O101 and fill down to suit.

In your other sheet:

  • sorry here's the sample sheet: drive.google.com/…
    – Eddie
    Aug 3, 2017 at 18:22
  • I changed : =FILTER({Sheet!A101:B201,Sheet!E101:S201},Sheet!O101:O201>0) to =FILTER({Sheet1!B101:C201,Sheet1!E101:FP201},Sheet1!FQ101:FQ201>0) Because this is the whole table
    – Eddie
    Aug 3, 2017 at 18:37
  • I would like to be able to pull data for one teacher (first name and last name name) Column B:C and the column headers that have a value.
    – Eddie
    Aug 3, 2017 at 18:39
  • This is new to me not sure what the transpose process would look like.
    – Eddie
    Aug 3, 2017 at 19:21
  • I can use the following formula to give me the first column name that contains a value using: =ArrayFormula(INDEX($E$1:$FP$1,MATCH(FALSE,ISBLANK(E2:FP2),0)))
    – Eddie
    Aug 3, 2017 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.