Formula help - if cell range contain any text, check the checkbox, otherwise uncheck

Jamie Null · 06/01/18

Hi All,

I'm new to Smartsheet and was hoping someone might be able to help. I'm trying to build a formula where if a range of cells with that row contain any text, a checkbox is checked. But if anyone of the cells in that range has not been filled out, the box is not checked.

Paul Newcome · 06/01/18

=IF(OR(ISTEXT([Column1]@row), ISTEXT([Column2]@row), ISTEXT([Column3]@row), ISTEXT([Column4]@row)), 1, 0)

Simply replace the column names with your column names and add/take away however many columns you need to. The column reference would be the following portion of the formula:

ISTEXT([Column#]@row),

Jamie Null · 06/14/18

Thank you for responding. I just saw your response.

This formula seems to check the box if any one of the columns contains text. Do you happen to know how to only have the box check when all the chosen columns contain text?

In the attached sheet there are three columns (Interested in Speaking, Private Mtg Space, Room Size Private Meeting Space.) If all three of these columns in the row contain text then I was hoping the box would check, but if one is blank or the text is deleted then the check box goes back to unchecked.

I also have an image in another column. Also in the next column (not shown) we would insert images. Is there a formula that we can include that would only check the box if the image is present?

smartsheet formula help.png

J. Craig Williams · 06/14/18

For all, you can change the OR() to AND()

But ... you can't have both. Either they all have text or at least one does not. Smartsheet does not have a toggle feature that would remember what it used to be. If any missing text should be checked, then AND() is what you are looking for.

For the image, check if it is blank (blank= no image)

=IF(ISBLANK([YourColumnName]@row), "No Image", "Image")

Craig

Paul Newcome · 06/14/18

I totally read that wrong. I read it as any cell in stead of all cells. This is why sleep is important. Haha.

=IF(AND(ISTEXT([Column1]@row), ISTEXT([Column2]@row), ISTEXT([Column3]@row), NOT(ISBLANK([Image Column]@row))), 1, 0)

That will roll everything into one string for you so that all 3 columns need text AND the image column needs a picture before the box is checked. If any one of the 4 is empty, the box will remain unchecked. Sorry for the confusion.

Formula help - if cell range contain any text, check the checkbox, otherwise uncheck

Comments

Help Article Resources

Categories