Help w/cell validation (true/false)

I'm having a bad case of the Monday's!

I have a formula that works but I need to modify/reduce it to fit my needs now.

I have a table that I look for certain phases and it it's there then "true" and if it's not then "false"

Currently the C/H/M Incorrect Helper format formula work:

=IF(COUNT(CHILDREN([Step Label]@row)) > 0, IF(OR(CONTAINS("SPD", [Step Label]@row), CONTAINS("SPD", CHILDREN([Step Label]@row)), CONTAINS("review", [Step Label]@row), CONTAINS("review", CHILDREN([Step Label]@row))), 0, 1), "-")

However, I now need 2 "reduced formulas" - 1 that looks for "contains SPD" in the Step Label column for C/H/M Incorrect format and 1 that looks for "contains Review" in the Step Label column for Low Incorrect Helper.

I tried:

=IF(COUNT(CHILDREN([Step Label]@row)) > 0, IF(OR(CONTAINS("review", [Step Label]@row), CONTAINS("review", CHILDREN([Step Label]@row)), 0, 1), "-") but it only ever returns a dash ( - ) in the Low Incorrect Helper column.

What am I missing?

Find more posts tagged with

Accepted answers

All comments

Ramzi K

@jmoser

Are any of your rows parents? If not, you will always get a "-".

Your formula states that if your row is not a parent then "-"

I hope that makes sense.

Cheers,

Ramzi

jmo

https://community.smartsheet.com/discussion/comment/265694#Comment_265694

Yes - I do have parent rows. Any idea how to clean this up a bit?

jmo

Maybe this helps a bit more?

I copy/pasted the formula from the C/H/M Incorrect format column into the Low Incorrect Helper and still get dashes in the last 2 rows (the only one where I pasted the formula)

It's the same formula in both columns with a different result:

The formula in the Incorrect helper column is:

=JOIN(CHILDREN([Step Label]@row), " , ") + " , " + [Step Label]@row

Ramzi K

@jmoser

Any chance you can temporarily share the sheet with me? Not sure how you can get different results with same formula unless you did a copy/paste of the cell and not the formula (i.e. edit the first cell, copy the formula, then edit the second cell and past the formula).

Cheers,

Ramzi

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.