Nested IF formula with Status colors

i am using a nested if formula, with HAS and a status ball colors.

If a person is trained on a SOP-Green (crosslinking data from sheet 1)

If not -Red (crosslinking data from sheet 1)

If author of the SOP- Yellow (crosslinking data from sheet 2)

Here is the current f-la, that works for Green and Red (same sheet), NOT for the Yellow status:( different sheet)

=IF(COUNTIFS({Copy of Copy of FOR902A Test 1 Name}, [Employee Name (First,Last)]@row, {FOR902A Test 1 Doc.Ttile}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Green", "" + IF(COUNTIFS({Authors}, [Employee Name (First,Last)]@row, {SOP name}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Yellow", "Red"))

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try switching the Yellow and Green IF statements so that Yellow comes first.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Nested IFs read from left to right and stop on the first true value. I made the assumption that yellows are also in the green list. If it is in the green list, it will stop evaluating there because it is true.


    Order of priorities depends on what you need for your particular use case. If you want something to override something else, it should come first.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    Try updating to this following:

    =IF(COUNTIFS({Copy of Copy of FOR902A Test 1 Name}, [Employee Name (First,Last)]@row, {FOR902A Test 1 Doc.Ttile}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Green", IF(COUNTIFS({Authors}, [Employee Name (First,Last)]@row, {SOP name}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Yellow", "Red"))

    If I'm reading it right, you don't want to add the second IF, you just want it to be the start of the previous IF false statement. So the "" + in the formula is not needed.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Vuckecucke
    Vuckecucke ✭✭
    edited 04/09/24

    thanks. I tried this and was getting #incorrect. now what worked to return the Yellow was having>1not>0 for"Green", but this made all the green change to red :(. ill have to see how the count works with nested if when cross-referencing multiple sheets.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    When you say the yellow isn't working, is it throwing an error, or is it just not registering at all?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    #INCORRECT ARGUMENT SET means that your ranges are different sizes. Edit your ranges to make sure they contain the same number of rows (or select the header to include the entire column)

    I was able to recreate this formula to have it work

    =IF(COUNTIFS({Copy of Copy of FOR902A Test 1 Name}, [Employee Name (First,Last)]@row, {FOR902A Test 1 Doc.Ttile}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Green", IF(COUNTIFS({Authors}, [Employee Name (First,Last)]@row, {SOP name}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Yellow", "Red"))

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • I copy pasted your formula and its not registering the yellow at all. My sheet 1 is list of employee's name( lets call it y axis) and than a drop down list of sops trained on (x axis). All info collected via forms.

    My authorship sheet(manually entered) is a list of Sop titles ( Y axis), and authors name (contact list collum) x axes.

    this also answers Pauls question. thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try switching the Yellow and Green IF statements so that Yellow comes first.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Vuckecucke
    Vuckecucke ✭✭
    edited 04/11/24

    It working!! thanks, why would this make it work though?

    if i wanted to include additional condition for the blue, would there be a specific order of priorities by color?

    Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Nested IFs read from left to right and stop on the first true value. I made the assumption that yellows are also in the green list. If it is in the green list, it will stop evaluating there because it is true.


    Order of priorities depends on what you need for your particular use case. If you want something to override something else, it should come first.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!