Help w/cell validation (true/false)

jmo
jmo ✭✭✭✭✭✭
in Formulas and Functions

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"

image.png

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?

· Share on FacebookShare on Twitter

Answers

  • Ramzi K
    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

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

    · Share on FacebookShare on Twitter
  • jmo
    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?

    · Share on FacebookShare on Twitter
  • jmo
    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)

    image.png

    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

    · Share on FacebookShare on Twitter
  • Ramzi K
    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

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!