1. Open up an ACT test to categorize.

2. Go to the QUESTIONS sheet, and select everything on it (CTRL+A on a PC, command+A on a Mac).

3. Go to Data > Data Validation; click Remove Validation and agree to whatever it asks you to do.

4. Replace everything in the topic names sheet with this sheet's contents. Make sure you leave the single blank row at the top––you can make sure it's selected by doing a select-all action twice.

5. Go to Tools > Script Editor.

6. In Code.gs paste the following:

var regExp = new RegExp(" |-|&|\/|\(|\)", "gi");

function buildDataValidation(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var question_sheet = ss.getSheets()[2];
  var topic_sheet = ss.getSheets()[4];

  var last_question_row = question_sheet.getLastRow();
  var last_topic_row = question_sheet.getLastRow();

  var subject_col_range = question_sheet.getRange("H2:H" + last_question_row)
  var subject_col_values = subject_col_range.getValues();
  var subjects_list = topic_sheet.getRange("A2:A" + last_topic_row);

  //Clear Named Ranges
  var namedRanges = ss.getNamedRanges();
  
  for (i = 0; i < namedRanges.length; i++){
    ss.removeNamedRange(namedRanges[i].getName());
  }

  for(j = 1; j < topic_sheet.getLastColumn(); j++){

    var thisVal = "Subject"
    var workingVal
    var totalRowsWithThisVal = 1

    for(i = 2; i < (last_topic_row + 2); i++){

      workingVal = topic_sheet.getRange(i, j).getValue().replace(regExp,"").toLowerCase();

      Logger.log('workingVal : ' + workingVal);

      if(workingVal != thisVal){
          Logger.log('Naming : ' + thisVal);
         ss.setNamedRange(thisVal, topic_sheet.getRange((i-totalRowsWithThisVal), (j+1), totalRowsWithThisVal) );
         thisVal = workingVal
         totalRowsWithThisVal = 0
      }

      totalRowsWithThisVal++

    }
  }

  //j is iterator over subject_col row values
  var j = 0

  //i is relative iterator over subject_col row values in the sheet itself
  for(i = 2; i <= last_question_row; i++){

    val = subject_col_values[j][0]

    Logger.log('in subject_col for loop, val : ' + val);

    if(val == "Reading Test"){
      val = "Reading"
      question_sheet.getRange("H" + i).setValue("Reading");

    }

    if(val == "Science Test"){
      val = "Science"
      question_sheet.getRange("H" + i).setValue("Science");

    }

    addDep(val, i, 9) 
    addDep(val, i, 11) 
    addDep(val, i, 13)

    j++

  }

  var subjects_rule = SpreadsheetApp.newDataValidation().requireValueInRange(subjects_list, true).build();
  subject_col_range.setDataValidation(subjects_rule);
}

function addDep (tVal, tRow, tCol){
  var nameOfRange = tVal.replace(regExp,"").toLowerCase();
  
  Logger.log('Adding dep @' + tRow + ',' + tCol + ':\t' + nameOfRange);

  var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(nameOfRange);
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var question_sheet = ss.getSheets()[2];

  var range = question_sheet.getRange(tRow, tCol);
  range.setDataValidation(rule);
}

function onEdit (){
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetName = sheet.getSheetName();
  var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var aColumn = aCell.getColumn();
  var aRow = aCell.getRow();
  Logger.log('SHEET : ' + sheetName + '\nCELL: ' + aCell + '\nCOLUMN: ' + aColumn + '\nROW: ' + aRow);
  if (sheetName == 'QUESTIONS' && aColumn == 8){
    var originalValue = aCell.getValue(); //Set topic validation
    Logger.log('ORIGINAL VALUE : ' + originalValue);
    addDep(originalValue, aRow, 9);
    addDep(originalValue, aRow, 11);
    addDep(originalValue, aRow, 13);
  } else if (sheetName == 'QUESTIONS' && (aColumn == 9 || aColumn == 11 || aColumn == 13)){
  var originalValue = aCell.getValue(); //Set subtopic validation
  addDep(originalValue, aRow, aColumn + 1);
  }
}

7. When you save, it's going to ask you to enter a project name. Enter something like "Data Validation".

8. Go to Run > Debug function > buildDataValidation.

9. On the modal window that pops up, click Review Permissions.

10. Continue through with your @sparkprep.com email address.

11. Click Advanced, and then click Go to Untitled project (unsafe).

12. Click Allow.

At this point, the script will run for a few minutes; once it's done, you can close out of this, go back to the Questions sheet and start categorizing.

Troubleshooting

If you run into any errors after running the script, chances are that one of the rows in the spreadsheet doesn't have a value in the Subject row set.

To find out which row:

1. In the Script Editor, go to View > Logs

2. Scroll down. You'll see a message that says "Adding dep @..."; the value after the @ sign is the row in the Questions sheet where there was a problem.

You can fix this by adding a Subject, or––if it's an empty row––deleting the entire row.