by Lou
Last Updated August 13, 2019 10:03 AM

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?

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

:

`=ArrayFormula(ROW(INDIRECT("A1:A"&A2)))`

Where `A2`

is the `n`

value

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.

- ServerfaultXchanger
- SuperuserXchanger
- UbuntuXchanger
- WebappsXchanger
- WebmastersXchanger
- ProgrammersXchanger
- DbaXchanger
- DrupalXchanger
- WordpressXchanger
- MagentoXchanger
- JoomlaXchanger
- AndroidXchanger
- AppleXchanger
- GameXchanger
- GamingXchanger
- BlenderXchanger
- UxXchanger
- CookingXchanger
- PhotoXchanger
- StatsXchanger
- MathXchanger
- DiyXchanger
- GisXchanger
- TexXchanger
- MetaXchanger
- ElectronicsXchanger
- StackoverflowXchanger
- BitcoinXchanger
- EthereumXcanger