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

Garethorus's avatar
Garethorus
Copper Contributor
Aug 24, 2021

Hi, i stumbled upon this and found solution...

For example

ABC
1Fund100 (user input)
2Splitter10 (user input)
3  

4

  
5formula *formula **



* =sequence(c2,1)
** =if(b5="","",if(b6="",#,##))
# =$c$1-sum($c$3:c4)
## =$c$3/$c$4+randbetween(-5,5)/100*($c$1/$c$2)

Legends,

YOU WILL HAVE TO COPY THE ## FORMULA MANUALLY (for ex, copy from c5 to c200) 
* will create sequence based on c2 input in rows, and 1 in columns
** will identify, if the cell left to it, b5, is blank, it will be blank, but if not blank (still in the sequence), it will give formula. Formula # if b6 is blank, meaning b5 is the LAST sequence. Formula ## if b6 is not blank, meaning b5 is still in the MID of sequence
# I have to make row 3 and 4 blank to manipulate formula to count for last cell, total fund, minus the sum of all the divider before this last cell
## formula to randomize the amount of fund splitted equally, but given -5 and 5 percent (per100) deviation

Resources