When you have many sumif formulas in your spreadsheet, this is usually a sign you need to use query instead. The script you are currently using to fetch the sheet names can be modified so that it creates a query formula instead:
function queryFormula() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formula = "=query({";
var sheets = ss.getSheets();
for (var i=1; i<sheets.length; i++) {
formula = formula + "'" + sheets[i].getName() + "'!A5:E;";
}
formula = formula.replace(/;$/, '}, "select Col1, Col2, sum(Col5) group by Col1, Col2")');
ss.getSheetByName("Totales").getRange("H2").setFormula(formula);
}
The result of running this script is that cell H2 gets the formula
=query({'02/26/2016 20:33:08 gcrosta@gmail.com'!A5:E;'02/26/2016 19:23:17 leyla.s.rodriguez@gmail.com'!A5:E;'02/26/2016 17:26:37 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:26 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:17 iris2908@fibertel.com.ar'!A5:E;'02/26/2016 17:05:49 iris2908@fibertel.com.ar'!A5:E}, "select Col1, Col2, sum(Col5) group by Col1, Col2")
which does everything else. The first parameter of the query is the array, which consists of the ranges A5:E put together, on top of one another.
The second parameter is a query string, which says to sum column 5 (which in the range A:E is E), grouping by columns 1, 2 (which are A,B).
The output looks like this:
+-------------------------------------------------+------+------+
| ACEITE DE oLIVA Escencias de la Tierra x 500 ml | 2215 | 582 |
| Aceite de Coco God Bless 325 g | 2488 | 900 |
| Aceite de Coco Refinado Napus 660 ml | 2626 | 840 |
| Aceite de Coco Virgen God Bless 225 g | 2479 | 900 |
| Aceite de Coco Virgen God Bless 500 g | 2387 | 1500 |
+-------------------------------------------------+------+------+
Updating the formula
If you rename the script function "queryFormula" into onOpen, it will run every time you open the spreadsheet, thus ensuring you get the current set of sheets in the formula.
how about to have a master sheet and create slave sheets (or spreadsheets) which will use =IMPORTRANGE from a master and each of your managers gets 1 such slave sheet (or spreadsheet) which can be filtered as pleased
then such sheets can be locked per manager and act as "dedicated filtering sheet" for such manager while master can be editable
Best Answer
in minutes:
in hours: