# Google Sheets populate X cells with formulas based on single cell containing value X?

Google sheets dynamically populate cells based on single cell value. I don't know what the term would be called and a bunch of searching hasn't resulted in any lead on what it would be called.

What I would like to do is populate `X` number of rows with the formulas based upon a cell that has the number value of `X`.

An example would be an amortization sheet for a loan. If you have a cell that contains the term years `15` that's 180 periods (`15*12`). The formula is the same for each column and dragging them down 180 rows would give me the same results it would auto-populate the cells. If I then changed the term cell value to `30` years loan I would have to drag it another 180 rows for a total of 360.

Instead, I'm trying to figure out how to have the sheet auto-fill the values based on the term cell number. It would then auto-populate the rows based on that value.

What is this term called or how could I go about doing this? Do I need to create my own macro in the sheets script?

You are supposed to use a cell reference of the cell you have a value in so instead `=15*12`
you do `=A\$1*12` and drag down: if you don't want to play with dragging you need to use array formula:

``=ARRAYFORMULA(IF(ROW(A1:A10), A\$1*12, ))`` • Yeah I'm doing a cell reference already. The arrayformula along with row does what I wanted `=ARRAYFORMULA(IF(ROW(A1:A100)>A\$1,"",A\$1))` May 21, 2019 at 18:54
• This is what I finally ended up with `=ARRAYFORMULA(IF(ROW(A1:A100)<=D\$2,ROW(A1:A),""))` It's a bit of a hack. I'm filling AX cells with incremental values starting at 1 based on what int value is in D\$2. Would you happen to know a better way to reference the cell above in an array formula? May 21, 2019 at 19:02