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:
https://infoinspired.com/google-docs/spreadsheet/auto-serial-numbering-in-google-sheets/
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?
Best Answer
EDIT :
The easiest way to generate an array of sequential number in Google Sheets is the
=Sequence
function.=Sequence(A2)
There's a way without dragging a cell with
Arrayformula
:=ArrayFormula(ROW(INDIRECT("A1:A"&A2)))
WhereA2
is then
valueThere's no incrementation in Google spreadsheet so the only way is to find a build-in counter : the rows/columns.
ROW()
andCOLUMN()
return the adress of a cell or an array with Arrayformula. Now your case is using a dynamic range, to do so we useINDIRECT()
Using
&
to concatenate the first row number (1) with then
value.