Google Spreadsheet, put data in a row based on whether a cell has an X in it

Question

I have a list of tutors in a Google Spreadsheet:

Sheet 1

Name | Address | Phone | Profile | Subject1 | Subject2 | Subject3 | ... | SubjectN

I would like to populate additional sheets with the name, address and phone data of each tutor that tutors a specific subject.

In sheet 1 I would put an X or 1 or whatever in the subject cell to indicate that the tutor covers that subject.

Each subject specific sheet would then be populated with the contact data of each tutor that covered that specific subject, but not others that did not have a mark in the corresponding cell in sheet 1.

This would give me a master list in sheet 1 that I could update, and subject specific sheets with a list of just the tutors that cover that specific subject. Most tutors cover more than one subject, but I do not need any sheets that reference multiple subjects.

I've seen various possible approaches for similar issues but none seem to match this situation.

Answer 1

If you want to avoid a script (which gives you a one-off update when you run it) and use a more dynamic approach (ie update instantly when you change the value of X in a subject tutors list), then the If and Vlookup functions are a good option.

Put your master list into alphabetical order, by the unique value that you want to identify your teachers by.

Then in each teacher-data cell in the teachers sheets say something like

=if (B1 = "X", vlookup (A1, master-list-data-range, 3, 0  ), "")

that's basically saying If X THEN find the value in cell A1 in the first column of master-list-data-range, and then to return the value from the 3rd colum.
ELSE show blank.

Answer 2

The easiest way to accomplish this is with a Google Apps Script. The following scripts defines a function that will list the name, address and phone number from the range given in tutorList:

function listTutors(subjectName, tutorList) {
  var tutorListColumnNames = tutorList[0]; // First row, which is column headers
  var subjectColumnIdx = undefined;
  for (var i = 0; i < tutorListColumnNames.length; i++) { // Find the index of the column for this subject
    if (tutorListColumnNames[i] == subjectName) {
      subjectColumnIdx = i;
      break;
    }
  }
  var result = [];
  if (subjectColumnIdx == undefined) { // Subject column not found
    result.push(["Error: No column for subject " + subjectName]);
  } else {
    for (var i = 1; i < tutorList.length; i++) {
      var row = tutorList[i];
      if (row[subjectColumnIdx] != "") { // If the column for this subject is non-empty, then
        result.push([row[0], row[1], row[2]]); // .. add name, address and phone to the result
      }
    }
  }
  return result;
}

To install the script, click Tools -> Script editor, and paste the above code.

To use the script, enter the formula

=listTutors("Subject1", Sheet1!A1:G)

into a cell, replacing Subject1 with the name of the subject whose tutors you want to list. The subject name must be one of the columns in Sheet1, or else an error message will be shown. Sheet1!A1:G is the range containing your tutor list.

I have set up an example spreadsheet to demonstrate the code.

EDIT: When thinking about it, it might be simpler to do this with a =QUERY:

=QUERY(Sheet1!A$2:G; "select A, B, C where E <> ''"; 0)

... where E is the column for Subject1. Substitute with F and G for Subject2 and Subject3. I have added an extra sheet to demonstrate this.