Google-sheets – Dynamic generation of cell list / array from range value in Google Spreadsheets

formulasgoogle sheets

I want to create an array/ a list of cells (i don't know the exactly terminology), in rows or columns form, covering the range between 2 numbers

Fox example, I have 2 cells,
A1 = 3; B1 = 10

I want to generate in a dynamic way, an array of cells that cover the range between A1 and B1:

C1 = 3
C2 = 4
C3 = 5
…
C8 = 10

Best Answer

I would use a script for this. Something like this:

function generateArray() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dest = sheet.getActiveRange();
  var destRow = dest.getRow();
  var destCol = dest.getColumn();
  var startValue = sheet.getRange(1, 1).getValue(); //Start value in cell A1
  var endValue = sheet.getRange(1, 2).getValue();   //End value in cell B1

  for (i = startValue; i <= endValue; i++){
    sheet.getRange(destRow, destCol).setValue(i);
    destRow++;
  }
}

Select the cell where you want the first number to appear and run the script.

If you really want to use a function you could use this:

=IF(AND(C1+1<=$B$1,C1<>""),C1+1,"")

Set the first cell (C1 in this case) to =A1 and paste the formula above in C2 and drag the corner down so that it's copied down.

The downside with the formula, especially if you have a big range, is that you have to drag it to cover the same amount of cells that is in the range. If you use the script ou just have to select the first cell and run it. An advantage with the formula, though, is that the cells update automatically. (You could modify the script to run automatically when you edit the range cells, though, if that is something you want.)

Related Topic