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 )

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 Oct 31 '20 at 4:43
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.

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 Oct 29 '20 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 Oct 30 '20 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 Oct 30 '20 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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