formulasgoogle sheetsregex
Looking for help referencing another spreadsheet, where I want to lookup several values in cell A2, to see if the value is contained in the cell (as there are variations), then setup an else clause at the end.
First crack isn't parsing:
=IF((SomeSheetName!A2=(REGEXMATCH("Johnny","Johnny")),
(SomeSheetName!A2=(REGEXMATCH("Dan","Dan")),"nosir")))
you could use a formula like:
=ARRAYFORMULA(FILTER(ROW(INDIRECT("A1:A20")), MOD(ROW(INDIRECT("A1:A20")), 2)=0))
A1:A20 represents range 1-202)=0 represents increment by 2Since 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.
Best Answer