Multiple criteria for If (contains) from list

I am creating a training matrix that will place an "x" in my cells if specific criteria (User Role) is met. The criteria differ per topic so I have created a reference form that I used in my formula.

=IF(OR($[User Role]11 = {Role to training matrix Range 1}, $[User Role]11 = {Role to training matrix Range 2}, $[User Role]@row = {Role to training matrix Range 3}, $[User Role]@row = {Role to training matrix Range 4}, $[User Role]@row = {Role to training matrix Range 5}), "X", "")

Is there any way to select the range that has my criteria instead of each one being individually referenced? This ability will help ensure if the criteria changes then I do not need to manually change every formula.



Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @CaseyMI,

    Try using this formula,

    =IF(CONTAINS([User Role]@row, {Role to training matrix Range}), "X")

    {Role to training matrix Range} is the entire column "Stock Raw Material Receiving" from the second sheet.

    Hope this helps,

    Dave

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!