Google Sheets Error Message – How to Create Error Message for Empty Cells

google sheets

I've been messing around with a spreadsheet for a while now and got it mostly working besides one part.

I have two columns, if a cell in the first column is "1", but the cell in the same row but different column is "empty" I want to output an error message.

I can do that for a single set of cells very easily with an IF, like so

=IF(J35 = 1,IF(ISBLANK(K35) = TRUE,"error","ok"),"ok")

But I have around 165 rows that I'd need to check that for.

Does anyone know how I could achieve this?

(The 1's and 0's are tickboxes in my actual table, ticked = 1, unticked = 0)

Best Answer

The use of the ARRAYFORMULA is what you need.
Please try the following formula instead
(Note adjust the ranges J3:J11 and K3:K11 to your needs and since you have tick-boxes change 1 to TRUE)

=ArrayFormula(IF(J3:J11 = TRUE,IF(ISBLANK(K3:K11) = TRUE,"error","ok"),"ok"))

Please read more about ARRAYFORMULA

Related Solutions

Google-sheets – How to make Query() not break with empty cells

Figured it out, adding a header of 0 to the query fixed it.

https://support.google.com/docs/answer/3093343?hl=en

QUERY(data, query, 0)

Google-sheets – Auto insert Formula on the next empty row upon form submit

Basically, the script should setformula on the first empty cell ('E2:E') in column E (in this scenario, the formula will place values on first three rows in column E range)

Use sheet.getLastRow() to find the last row coordinate with data, then add 1 to get the first empty row coordinate and get the range to post the formula.

then rechecks the column E again to find the first empty cell (which will be E5) and loop the incremented formula

Before rechecking could be necessary to do a Spreadsheet.flush() to update the spreadsheet with the change made previously.

Another approach could be to get all the required formulas, make an array of formulas and use setFormulas() to add all of then at once.

Best Answer

Related Solutions

Google-sheets – How to make Query() not break with empty cells

Google-sheets – Auto insert Formula on the next empty row upon form submit

Related Topic