Formula missing when Adding Rows in Google Sheets

Question

I have made a simple formula, based on cells in the same row, to indicate to the user that they have entered enough information - and it works as needed .

=IF(AND(ISTEXT(B20),ISNUMBER(C20),ISNUMBER(E20),ISTEXT(F20)),"Ok",IF(AND(ISBLANK(B20),ISBLANK(C20),ISBLANK(D20),ISBLANK(E20),ISBLANK(F20)),"","MORE INFO"))

However, when I click the built in "add more rows" button at the bottom of the sheet the formula is not present in the new cell - but any dropdown menu or validation I've used in the cells not containing the formula are present in the respective added cells of the new row. Any Ideas why just the formula is missing ? Thanks.

Answer 1

Thanks to GimelG at Google Docs Forum..........

When you add additional rows, Sheets assumes you want to continue the same dataset and, therefore, applies the existing formatting as well as Data Validation. It does not, however, copy formulas automatically as that would be adding actual new data to your sheet.

You can use an ArrayFormula version of your formula to populate the entire column, which it would continue to do as you'll add more rows of data. Assuming your data starts at row 2 (below the header), try this:

=arrayformula(if(istext(B2:B)*n(C2:C)*n(E2:E)*istext(F2:F),"Ok",if(len(B2:B)+len(C2:C)+len(D2:D)+len(E2:E)+len(F2:F),"MORE INFO",)))