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

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.

Find more posts tagged with

Formulas

Legal

Support

Comments

Paul Newcome

=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

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

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.