Help w/selecting multiple columns @row that are Blank

jmo

Hi team - I have a large span of columns that I need to look across to determine if any of the row columns are blank.

Can you select a range, like [Column1]:[Column100], and determine if any blanks exists without having to do a ISBLANK for every column @row?

I've used this in the past for a check box column formula but I have way too many columns to do ISBLANK for each:

=IF(OR(ISBLANK([Column1]@row), ISBLANK(Column2]@row), ISBLANK([Column3]@row), ISBLANK([Column4]@row)), true, false)

Thanks in advance,

Jeff

John Jonassen

@jmoser Try redoing the formula as if you were writing it from scratch, only rather than typing out the "([Description Information]@row:[Manual workaround effective period]@row" portion...click and drag through the entire selection...we may have some column naming issues or something along those lines.

John Jonassen

You could do something as simple as

=IF(COUNTIF([Column1]@row:[Column100]@row, "") > 0, "INCOMPLETE", "")

Edited: I just noticed that you have used it in a checkbox in the past, so maybe in a checkbox formatted column, you could do

=IF(COUNTIF([2020 Reimage]@row:[Column12]@row, "") > 0, 0, 1) and the checkbox would appear if there were null cells/columns within that row.

jmo

@John Jonassen - so I cleaned up my formula to replicate yours and I get an UNPARSABLE in the Complete column:

=IF(COUNTIF([Description Information]@row:[Manual workaround effective period]@row, “”) > 0, 0, 1)

[Description Information] is the first column to look at and [Manual workaround effective period] is the last column to look at. Anything in between there must have info else mark the column as Complete = false.

I guess I'm not understanding the COUNTIF incorporation into the IF function.

John Jonassen

@jmoser The countif is looking at the entire row, from your "Description Information" column through to and including your "Manual Workaround Effective Period" column, counting how many cells in that range are "" (blank/null). If there are more than 0 cells in that row, then you would get the true result of 0/unchecked, otherwise the false result of 1/checked.

You are, however, missing a closed paren:

=IF(COUNTIF([Description Information]@row:[Manual workaround effective period]@row, “”) > 0), 0, 1)

jmo

@John Jonassen - copy/pasted your adjust formula: =IF(COUNTIF([Description Information]@row:[Manual workaround effective period]@row, “”) > 0), 0, 1)

Still getting UNPARSEABLE error. :-(

Not sure what's going on.

John Jonassen

@jmoser Try redoing the formula as if you were writing it from scratch, only rather than typing out the "([Description Information]@row:[Manual workaround effective period]@row" portion...click and drag through the entire selection...we may have some column naming issues or something along those lines.

jmo

That did it @John Jonassen! Not sure what I was missing before but doing the "drag" over the columns to cover worked perfectly!