I want to produce an array of numbers from 1 to n, where n is a value in another cell. So if the cell value is 10, I want to generate an array of the numbers 1 to 10.

I found this resource which appears to answer a similar question:


But I think that it requires you to drag down the fill handle, also I didn't really understand from the resource how to apply this formula to generating n numbers. I want a formula which generates the numbers 1 to n without using the fill handle.

Is there a way to do this?


There's a way without dragging a cell with Arrayformula :

=ArrayFormula(ROW(INDIRECT("A1:A"&A2))) Where A2 is the n value

enter image description here

There's no incrementation in Google spreadsheet so the only way is to find a build-in counter : the rows/columns.
ROW() and COLUMN() return the adress of a cell or an array with Arrayformula. Now your case is using a dynamic range, to do so we use INDIRECT()

INDIRECT() returns a cell reference specified by a string.

Using & to concatenate the first row number (1) with the n value.

|improve this answer|||||
  • 1
    Perfect, does exactly what I need it to do. Thank you! – Lou Aug 13 '19 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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