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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…