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

Question

I have a 4 page spreadsheet with labeled columns at the top of each page.&nbsp; 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.

hansvogelaar · Answer

frontdesk835&nbsp;
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)

frontdesk835 · Answer

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!

hansvogelaar · Answer

frontdesk835&nbsp;
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" &amp; r).Value = n + i
        r = r + 1
        If r Mod 39 = 1 Then
            r = r + 1
        End If
    Next i
End Sub

Forum Discussion

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

Share

Resources