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?

1 Answer 1


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))
    – krizzo
    Commented 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?
    – krizzo
    Commented May 21, 2019 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.