Help w/cell validation (true/false)

jmo

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?

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:

=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), "-")

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