Google-sheets – Is it possible to use a Google Sheets formula to control the value inside of an inserted text box (drawing)

Your problem comes not from using named ranges, but that the multiplication =INDIRECT(X1)*C1 only uses the first row. =INDIRECT("B1:B")*C1 gives the same result.

I'm not sure what you're trying to do, but if you want a list of values, you could wrap the expression in ArrayFormula: =ARRAYFORMULA(INDIRECT(X1)*C1). This will fill column D with values from column B, multiplied by C1.

If you instead want the sum of all values from September, multiplied by C1, you would do something like: =SUM(INDIRECT(X1)*C1).

I've cooked up an example spreadsheet here.

Since the content of L7 is going to be overwritten, spreadsheet formulas cannot perform such reset. You need a script that has the spreadsheet keys of all the spreadsheets to be changed, and has the authority to edit them (i.e., the Google account under which it runs has this authority). It can be either a stand-alone script, or bound to your master spreadsheet, whichever is more convenient. Also, you'll need to know the name of the sheet within the spreadsheet in which the cell L7 is located; apparently it's "FailingStudents".

function reset() {
  var keys = ['....', '....' ]; // list of spreadsheet ids
  for (var i = 0; i < keys.length; i++) {
    var ss = SpreadsheetApp.openById(keys[i]);
    var sheet = ss.getSheetByName('FailingStudents');
    sheet.getRange('L7').setValue('No');
  }
}

Once you run this, the cell FailingStudents!L7 will be set to No in all listed spreadsheets.