Forum Discussion

tazewellj's avatar
tazewellj
Copper Contributor
Aug 08, 2019
Solved

Creating a formula to autofill a range of cells based on input

Hello, I am trying to create a formula that takes the input from a cell and fills a range of cells based on the numerical value entered. I'm not sure if there is a default function to a EX: If ...
admin
BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
  • PReagan's avatar
    PReagan
    Aug 08, 2019

    Hello tazewellj,

     

    Enter this formula into cell A2 and use cell B1 to input your number:

     

    =IF(ROW(A2)-1>$B$1,"",ROW(A2)-1)

     

    Then copy the formula down as necessary.

     

    Hope this helps!
    PReagan

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 08, 2019

tazewellj 

There is a default function that does precisely what you have specified but, unfortunately, it is only just now becoming available and that to Office 365 users only.

 

If you name the number cell 'n' (as you describe the problem it is cell $B$1 but there is no reason it shouldn't move), the sequence {1; ...; n} is generated by

= SEQUENCE( n )

The Dynamic Array formula is entered into a single cell and spills down to display the values.  To generate a horizontal number sequence the dimensions of the output range change to read

= SEQUENCE( 1, n )

 

There are further parameters that specify the start value and the step, 1 being the default in each case.

Resources