Forum Discussion

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
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

mayurana

Copper Contributor

Oct 24, 2019

Hello PReagan

Thanks for the earlier response .. this was also something i was looking for but i had an additional requirement.

In the response it was mentioned that we have to drag the formula down to the required number of cells in column A.

However can it be possible that say N number of Rows automatically fill based on the value of N entered in B2 e.g. if a person enters 512 in B2, A2:A513 all gets a number starting from 1 and incremented downwards .. ( without have to drag the formula all the way to A513 ) the number N could be any value (no max limits)

PReagan

Bronze Contributor

Oct 24, 2019

mayurana

This would require VBA if you wish to fill N number of cells with the formula.

Alternatively, as @Peter Bartholomew suggested, you may "take your existing formula and fill it down beyond your range of interest;" (for example cell A1048576) " a slight modification would return "" for values you do not require." The downside to this, however, is that this would slow down Excel.