Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.)

share|improve this answer

A formula that could be placed in any column (other than A or B) in Row1 and that does not require C1 to be seeded with the A1 value:

=if(row()<B$1-A$1+2,A$1+row()-1,"")  

However, as with @Punchlinern's, needs to be copied down far enough to occupy at least one more row than the difference between B1 and A1.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.