Auto-fill formula

1
votes

I've been trying to put a formula in Google Sheets and I have a column of cells with blanks and non blanks. I'm using the ISBLANK function to tell if the cell is blank or not, but however I need the formula to fill in when a new row is inserted. I tried the ARRAYFORMULA function, but it doesn't work with the ISBLANK function.

Error: I have a Google Form hooked up to my spreadsheet. In column H, I have the answers, yes and no. In Column S, I have the function ISBLANK hooked up to the appropriate cell in column H (eg. H2 to S2), because I need to know if that cell is blank or not. However, Google Forms inserts a new row when new data is received, so the formula is missing in column S (because a new row got inserted). I tried ISARRAY(ISBLANK(H2)), but all I get is FALSE in H2 and nothing below it.

Any suggestions?

1
google-sheets
ISBLANK() works with arrayformula. Can you show an example and go into detail about the error? - Robin Gertenbach
Sorry, that was all StackOverflow produced. I'm new here. - K.Gu
Edited with better desc. of error - K.Gu

1 Answers

0
votes

In cell S2, try:

=ARRAYFORMULA(ISBLANK(H2:H))

or, assuming Column A is a column of index values, ie it's never blank, you can neaten it up with this in cell S2:

=ARRAYFORMULA(ISBLANK(H2:INDIRECT("H"&(1 + COUNTA(A2:A)))))