Trying to get RYGB to work with two columns

If the PO Process Status is "8 - PO issued" use Blue Harvey Ball.

Formula that works: =IF([PO Process Status]@row = "8 - PO issued", "Blue")

If the Pending as of date is less than x etc. use Harvey Ball...

Formula that works: =IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))

How do we combine? We have tried IF(AND, IF(AND(OR, IF(OR - nothing is working.

Best Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    If I'm understanding this correctly, if the PO is issued, you want it to be blue. Otherwise, if it's pending, you want another color based on a date. Is that right? If so, give this a try:

    =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green")))

    Let me know if that helps!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Hi @SandyK_GE - I would use the ISBLANK function instead. It would look like this. Let me know if this works for you!

    =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(ISBLANK([Pending As Of]@row), "", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Oh! Delete all the closing parentheses at the end and try again. Sometimes if you have too few or too many, it does that. If there are none, it will auto-create the right amount.

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • SandyK_GE
    SandyK_GE ✭✭
    Answer ✓

    Thank you. Works! Can't thank you enough.

    This formula works: =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green")))

    This formula works: =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(ISBLANK([Pending As Of]@row), "", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))))

    If above formula fails the first time, remove all right parens at the end of the formula. Once you press enter, it will work.

Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    If I'm understanding this correctly, if the PO is issued, you want it to be blue. Otherwise, if it's pending, you want another color based on a date. Is that right? If so, give this a try:

    =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green")))

    Let me know if that helps!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • SandyK_GE
    SandyK_GE ✭✭
    edited 04/19/23

    Thank you! This formula worked.

    So now, I need to say if this: If Pending as of is blank, make the cell blank. But if PO issued is true, make cell blue. But if Pending make cell color based on date.

    =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF([Pending As Of]@row = "", "", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))))

    I think this formula work. Can you confirm?

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Hi @SandyK_GE - I would use the ISBLANK function instead. It would look like this. Let me know if this works for you!

    =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(ISBLANK([Pending As Of]@row), "", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Amber: Did not work. Get #unparsable. I appreciate your help though. S

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Oh! Delete all the closing parentheses at the end and try again. Sometimes if you have too few or too many, it does that. If there are none, it will auto-create the right amount.

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • SandyK_GE
    SandyK_GE ✭✭
    Answer ✓

    Thank you. Works! Can't thank you enough.

    This formula works: =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green")))

    This formula works: =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(ISBLANK([Pending As Of]@row), "", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))))

    If above formula fails the first time, remove all right parens at the end of the formula. Once you press enter, it will work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!