How do I enter a number in one cell that automatically fills all cells below numerically

Copper Contributor

I have a 4 page spreadsheet with labeled columns at the top of each page.  I need help on how to formulate the first cell in a column on page 1 so that all cells below automatically fill in numerical order across all 4 pages.

3 Replies

@frontdesk835 

Enter a formula like this in the cell in row 2:

=SEQUENCE(number_of_rows, , first_number)

For example, if you want 100 rows, with the numbers 101, 102, ..., 200:

=SEQUENCE(100, , 101)

Hans, thank you for getting me on the right track. I am still struggling with the auto population. Here is the formula I am entering: =SEQUENCE(D2:D156(-D40,D79,D118), 1, 50005, 1)

I am using column D for these entries D2:D156 (omitting D40, D79, D118).
I want the first row to start with 50005, and each row below to increase by 1.
Where am I going wrong?
Thanks!

@frontdesk835 

Someone else will no doubt come up with a LAMBDA formula. In the meantime here is a macro you can run after entering a value in D2:

Sub Fill()
    Dim n As Long
    Dim i As Long
    Dim r As Long
    n = Range("D2").Value
    Range("D3:D153").ClearContents
    r = 3
    For i = 1 To 151
        Range("D" & r).Value = n + i
        r = r + 1
        If r Mod 39 = 1 Then
            r = r + 1
        End If
    Next i
End Sub