Google-sheets – Making dynamic number of arguments based on a specific criteria for importrange function

google sheets

I am counting number of cases per day in each month by importrange data from daily sheets in every month with this formula on D3 for example:

=SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE($Q$7,R5),IMPORTRANGE($Q$8,R5),IMPORTRANGE($Q$9,R5),IMPORTRANGE($Q$10,R5),IMPORTRANGE($Q$11,R5),IMPORTRANGE($Q$12,R5),IMPORTRANGE($Q$13,R5),IMPORTRANGE($Q$14,R5),IMPORTRANGE($Q$15,R5),IMPORTRANGE($Q$16,R5),IMPORTRANGE($Q$17,R5),IMPORTRANGE($Q$18,R5),IMPORTRANGE($Q$19,R5),IMPORTRANGE($Q$20,R5),IMPORTRANGE($Q$21,R5),IMPORTRANGE($Q$22,R5),IMPORTRANGE($Q$23,R5),IMPORTRANGE($Q$24,R5),IMPORTRANGE($Q$25,R5),IMPORTRANGE($Q$26,R5),IMPORTRANGE($Q$27,R5),IMPORTRANGE($Q$28,R5),IMPORTRANGE($Q$29,R5),IMPORTRANGE($Q$30,R5),IMPORTRANGE($Q$31,R5),IMPORTRANGE($Q$32,R5))

Rows from Q5:Q32 (28 rows) represent IDs of daily sheets for the month of February (C1) & Rows R5:R36 are cells that contain the numbers to be summed. Those cells R5:R36 are fixed in all daily sheets. For another month like January, the number of rows will be 31 (Q5:Q35) where I need to aextend the previous formula to : Importrange(Q33,R5),Importrange(Q34,R5),Importrange(Q35,R5)) . I am looking for an idea on how to make the formula fixed so I don't need to change the number of arguments based on number of rows (which represent daily sheet IDs). It makes sense for me so that I can make this sheet a master sheet where the data will change whenever the text in C1 is changed to a different month otherwise the formula will show error message if the number of ID rows are less than the number of arguments in the formula (for example, importrange (Q5,R5),…. importrange(Q35:R5) for February where the IDs ends on row R32).

I thought on using sumif() using criteria like if C1=Januaray or March or May or any month contains 31 days then 30 days then 28 days!

Best Answer

I could Use this trick on the counter cells =ifs(OR(ActiveMonth="January",ActiveMonth="March",ActiveMonth="May",ActiveMonth="July",ActiveMonth="August",ActiveMonth="October",ActiveMonth="December"),Y1,OR(ActiveMonth="April",ActiveMonth="June",ActiveMonth="September",ActiveMonth="November"),Z1,OR(ActiveMonth="February"),AA1), where I move down one value of cells (Y1,Y2..etc), (Z1,Z2.. etc)

For Column Y, this formula on 35 rows: =SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE($Q$7,R5),IMPORTRANGE($Q$8,R5),IMPORTRANGE($Q$9,R5),IMPORTRANGE($Q$10,R5),IMPORTRANGE($Q$11,R5),IMPORTRANGE($Q$12,R5),IMPORTRANGE($Q$13,R5),IMPORTRANGE($Q$14,R5),IMPORTRANGE($Q$15,R5),IMPORTRANGE($Q$16,R5),IMPORTRANGE($Q$17,R5),IMPORTRANGE($Q$18,R5),IMPORTRANGE($Q$19,R5),IMPORTRANGE($Q$20,R5),IMPORTRANGE($Q$21,R5),IMPORTRANGE($Q$22,R5),IMPORTRANGE($Q$23,R5),IMPORTRANGE($Q$24,R5),IMPORTRANGE($Q$25,R5),IMPORTRANGE($Q$26,R5),IMPORTRANGE($Q$27,R5),IMPORTRANGE($Q$28,R5),IMPORTRANGE($Q$29,R5),IMPORTRANGE($Q$30,R5),IMPORTRANGE($Q$31,R5),IMPORTRANGE($Q$32,R5),IMPORTRANGE($Q$33,R5),IMPORTRANGE($Q$34,R5),IMPORTRANGE($Q$35,R5))

On Column Z, this formula on 35 rows: =SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE($Q$7,R5),IMPORTRANGE($Q$8,R5),IMPORTRANGE($Q$9,R5),IMPORTRANGE($Q$10,R5),IMPORTRANGE($Q$11,R5),IMPORTRANGE($Q$12,R5),IMPORTRANGE($Q$13,R5),IMPORTRANGE($Q$14,R5),IMPORTRANGE($Q$15,R5),IMPORTRANGE($Q$16,R5),IMPORTRANGE($Q$17,R5),IMPORTRANGE($Q$18,R5),IMPORTRANGE($Q$19,R5),IMPORTRANGE($Q$20,R5),IMPORTRANGE($Q$21,R5),IMPORTRANGE($Q$22,R5),IMPORTRANGE($Q$23,R5),IMPORTRANGE($Q$24,R5),IMPORTRANGE($Q$25,R5),IMPORTRANGE($Q$26,R5),IMPORTRANGE($Q$27,R5),IMPORTRANGE($Q$28,R5),IMPORTRANGE($Q$29,R5),IMPORTRANGE($Q$30,R5),IMPORTRANGE($Q$31,R5),IMPORTRANGE($Q$32,R5),IMPORTRANGE($Q$33,R5),IMPORTRANGE($Q$34,R5))

On Column AA, this formula of 35 rows: =SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE($Q$7,R5),IMPORTRANGE($Q$8,R5),IMPORTRANGE($Q$9,R5),IMPORTRANGE($Q$10,R5),IMPORTRANGE($Q$11,R5),IMPORTRANGE($Q$12,R5),IMPORTRANGE($Q$13,R5),IMPORTRANGE($Q$14,R5),IMPORTRANGE($Q$15,R5),IMPORTRANGE($Q$16,R5),IMPORTRANGE($Q$17,R5),IMPORTRANGE($Q$18,R5),IMPORTRANGE($Q$19,R5),IMPORTRANGE($Q$20,R5),IMPORTRANGE($Q$21,R5),IMPORTRANGE($Q$22,R5),IMPORTRANGE($Q$23,R5),IMPORTRANGE($Q$24,R5),IMPORTRANGE($Q$25,R5),IMPORTRANGE($Q$26,R5),IMPORTRANGE($Q$27,R5),IMPORTRANGE($Q$28,R5),IMPORTRANGE($Q$29,R5),IMPORTRANGE($Q$30,R5),IMPORTRANGE($Q$31,R5),IMPORTRANGE($Q$32,R5))

Column Y sums values across 31 sheets Column Z sums values across 30 sheets Column Z sums values across 28 sheets

Related Solutions

Google-sheets – How to use a named column in a calculation based on a cell value in Google Spreadsheet

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.

Google-sheets – How to copy related row data from Google Sheets remote tab or Sheet into current Sheet with a script

Here is an example of how to do this. When "update" or "edit" is selected from the dropdown boxes, data from the other, designated tab's rows are copied into the current sheet.

Here is the script:

function onEdit(e) {

////UPDATE ROWS WITH DATA FROM REMOTE SHEET VIA SCRIPT
///the script below is adapted from https://mashe.hawksey.info/2015/03/google-sheets-move-a-row-of-data-to-another-sheet-based-on-cell-value/
  // see Sheet event objects docs
  // https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getSheetByName("Animals List");
var r = e.range;
var remoteSheet = ss.getSheetByName("List B");

  // to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;

  // Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;

var curNumberCell = s.getRange(rowIndex,2, 1, 1);
var curIDNumber =  curNumberCell.getValue();
var curDCell = s.getRange(rowIndex,4, 1, 1);
var curECell = s.getRange(rowIndex,5, 1, 1);

 // if our action/status col is changed to update or edit, do stuff
if (e.value == "update" || "edit" && colIndex == actionCol) {

 //THE FOLLOWING REPLACES THE FORMULAS OF THE ROW WITH THEIR GENERATED VALUES - UNCOMMENT TO USE:
 //var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
 //sourceRange.setValues(sourceRange.getValues());


///Retrieve data from remote tab/sheet that has matching cell value
var values = remoteSheet.getDataRange().getValues(); //IN REMOTE SHEET WE NEED TO SEARCH ENTIRE SHEET

for (var i = 0; i < values.length; i++) {
   if (values[i][0] == curIDNumber )
   {
   curDCell.setValue(values[i][1] );
   curECell.setValue(values[i][2] );
   }
   }    
} //end retrieve remote data
}

Best Answer

Related Solutions

Google-sheets – How to use a named column in a calculation based on a cell value in Google Spreadsheet

Google-sheets – How to copy related row data from Google Sheets remote tab or Sheet into current Sheet with a script

Related Topic