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

tazewellj's avatar
tazewellj
Copper Contributor
Aug 08, 2019

PReagan I'm not sure the ROW function will achieve this. The ROW function returns the value of the row. The formula you provided will always return a value of 1 no matter what. 

PReagan's avatar
PReagan
Bronze Contributor
Aug 08, 2019
The row function Row(A2)-1 will always be equal to 1 for cell A2 unless B1=0. When you copy the formula down to cell A3, the function becomes Row(A3)-1=2. Copied down again to cell A4 the formula becomes Row(A4)-1=3. The formula continues to add one to the previous total until it has reached a value greater than B1 at which point “” will be returned.

Unless I am misunderstanding your problem, this should return your desired result.
  • tazewellj's avatar
    tazewellj
    Copper Contributor
    Aug 08, 2019

    PReagan your right! This worked like a charm. I needed to drag the formula down for it to apply the way I wanted. Thanks so much for you assistance.

    • PReagan's avatar
      PReagan
      Bronze Contributor
      Aug 08, 2019

      Happy to help 🙂🙂

Resources