0

My Google Sheet has a text header, and then a few columns (let's say F and I) have a formula in them.
So as an example row 1 is all text, and row 2 would have =LEFT(H2,4)&" "&MID(H2,5,6). This was dragged down (auto filled) below so row 3 has H3 in the formula etc. all the way to say 100.

If I go insert a row at 50, is there a way for it to automatically fill in the formula H50. (Currently when I insert row, all the cells are blank).

Two 'test case' formulas:

=LEFT(H2,4)&" "&MID(H2,5,6)&"!"

=IF (ISBLANK(H2),"", ARRAY_CONSTRAIN(ARRAYFORMULA(IF( (MOD(SUM(INT(MID(REPT("0",20-LEN(H2))&H2,ROW($1:$31),1)*(MOD(ROW($1:$31),2)+1)/10)+MOD(MID(REPT("0",20-LEN(H2))&H2,ROW($1:$31),1)*(MOD(ROW($1:$31),2)+1),10)),10)=0), "✔", "❌")), 1, 1))

(This formula is from here - to test out in your own spreadsheet, H simply needs any valid credit card number such as 343280696646912 )

Improve this question
1
  • I respectfully suggest that this question is no longer a "general" question about repeating a simple formula in every row. Instead, the real "problem" is your credit card formula, and how to adapt/rewrite this so that he automatically repeats in every row. You should write a new question about this; I suggest that you ask the question on StackOverflow (not WebApps) because it has a much bigger group of volunteers which increases the odds of getting an answer to your question.
    – Tedinoz
    Commented Oct 31, 2020 at 4:43

1 Answer 1

Reset to default
2

Try: =arrayformula(LEFT(H2:H100,4)&" "&MID(H2:H100,5,6)) in the cell B2.

This will automatically populate cells "B2:B100" with the formula. If you insert a new row, the cell in Column B will populate with the formula.

Improve this answer
3
  • Thank you, this is working great for the simple formula, but I've added a very complex formula above that uses array_constrain and arrayformula already. Wrapping it in ArrayFormula: =ARRAYFORMULA( IF (ISBLANK(H2:H1000),"", ARRAY_CONSTRAIN(ARRAYFORMULA(IF( (MOD(SUM(INT(MID(REPT("0",20-LEN(H2:H1000))&H2:H1000,ROW($1:$31),1)*(MOD(ROW($1:$31),2)+1)/10)+MOD(MID(REPT("0",20-LEN(H2:H1000))&H2:H1000,ROW($1:$31),1)*(MOD(ROW($1:$31),2)+1),10)),10)=0), "✔", "❌")), 1, 1))) gives Error Function REPT parameter 2 value is negative. It should be positive or zero.
    – CeePlusPlus
    Commented Oct 29, 2020 at 2:31
  • If you are using this complex formula based on Arrayformula, why didn’t you just apply arrayformula to your “simple” formula. In any event, I suggest that you look to move the “if is blank” (or an equivalent statement) to within your own formula rather than add an additional layer of arrayformula.
    – Tedinoz
    Commented Oct 30, 2020 at 9:19
  • Sorry can you please elaborate? (Potentially with examples, once I use array constrain and formula I get confused). My goal is to simply get the ✔ or ❌ at the end, but only if the cell has data, otherwise blank.
    – CeePlusPlus
    Commented Oct 30, 2020 at 17:55

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.