Combining Not Contains and Contains into the same formula

hi Team,

The below formula. I am trying to combine these validations into one statement. If any of them are true, the row should flag as yellow. If any of them are false, it should flag as green. However, the statement is flagging as "green" when "warning records" is not found. It should flag as yellow.

________________________________________________________________________________________________

This is the content that should result in the row being "yellow":

Error records:

==============

Warning records:

================"

___________________________________________________________________________________________________

This is the statement that appears to be wrong. The "Contains" flag yellow properly, but the "Not Contains" does not. Does anyone know how this statement should be corrected so that if any of these conditions are met, it flags as "yellow". If none of them are met, it should flag as green.

=IF(LEN(Body@row) < 1, "RED", IF(OR(CONTAINS("Injury Date", Body@row), CONTAINS("Client", Body@row), CONTAINS("saving claim", Body@row), CONTAINS("ReadOnly", Body@row), CONTAINS("User Exception", Body@row), CONTAINS("not a Active/Reporting Location.", Body@row), CONTAINS("Request Channel", Body@row)), "Yellow", IF(NOT(CONTAINS("Warning message:", Body@row)), "Yellow", "Green")))

Find more posts tagged with

Formulas

Accepted answers

All comments

Carson Penticuff

I am a little confused on what you are asking. Let's walk through what the formula is currently doing and maybe you can explain what you would like for it to do differently.

If the "length" of the entry in the cell is less than 1 character (so essentially, if it is blank), the result should be red
If the entry contains any of the following text, {"Injury Date", "Client", "saving claim", "ReadOnly", "User Exception", not a Active/Reporting Location.", "Request Channel"}, the result should be yellow
If the entry contains "Warning message:", the result should be green

BJIloveSmartSheet

what you wrote is exactly what it should be doing. However, the formula I wrote is not doing that. It's flagging the last entry "warning message" as green, when the entry doesn't contain "warning message" Therefore, It should be yellow. Thanks so much.

Carson Penticuff

Can you share some specific examples of entries that are being flagged as green incorrectly? I threw your formula into a test sheet and it seems to work fine as written. The only change I made was to change RED to Red so that the "light" formatting worked. (I was assuming that was your setup)

BJIloveSmartSheet

Snag_efb4e8f.png

Yes attached is an example of where it should flag green and where it should flag as yellow. thanks again.

Carson Penticuff

I simplified it a bit, but the logic didn't really change so I wouldn't expect the outcome to be different:

=IF(LEN(Body@row) < 1, "Red", IF(OR(CONTAINS("Injury Date", Body@row), CONTAINS("Client", Body@row), CONTAINS("saving claim", Body@row), CONTAINS("ReadOnly", Body@row), CONTAINS("User Exception", Body@row), CONTAINS("not a Active/Reporting Location.", Body@row), CONTAINS("Request Channel", Body@row), NOT(CONTAINS("Warning message:", Body@row))), "Yellow", "Green"))

In the left image, is it possible any of the other conditions you are checking for are present, and that is perhaps causing the yellow return? I do not see any reason the image on the right should return Green.

How is this data being inputted to Smartsheet, copy + paste?

BJIloveSmartSheet

Thank yo so much, that did the trick!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.