# Trying to get RYGB to work with two columns

Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Options

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.

• ✭✭✭✭✭✭
Options

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

• edited 04/19/23
Options

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?

• ✭✭✭✭✭✭
Options

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

• Options

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

• Options
• ✭✭✭✭✭✭
Options

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

Options

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!