RYG - Checkbox/Age Group

Adriane Price

Hello,

I am having an issue with figuring out how to create a formula for RYG and checkbox columns. I have previously set this formula to be based on 'open' and 'completed' requests. Since this has changed we now have 'open', 'pending', 'at risk', and 'completed' requests.

The formula, in the beginning, was based on 'open' and 'completed' requests only.

Here is a screenshot as I have not yet changed my formula since this is in production:

The "PowerStore Shadow Complete" is manually check-marked by the user. If it is not check marked "PS Shadow Status" is 'open', if it is check marked "PS Shadow Status" is 'completed' this automates based on the lookup sheet. Example: When the user checks the box in the "PowerStore Shadow Complete" column the "PS Shadow Status" column refers to my lookup sheet with this formula: =VLOOKUP([PowerStore Shadow Complete]@row, {PS Shadow Lookup Range 1}, 2, false)

The "Risk Indicator" column is based on the "Age Group" column when certain days hit then the "Risk Indicator" column reflects the RYG ball.

This is the formula used in the "Risk Indicator" column: =IF([Age Group]@row = "0-7 days", "Green", IF([Age Group]@row = "8-14 days", "Yellow", IF([Age Group]@row = "15-20 days", "Red", IF([Age Group]@row = "21+ days", "Red"))))

Now that we have 'open', 'pending', 'at risk', and 'completed' requests I need the "PS Shadow Status" to indicate  'open', 'pending', 'at risk', and 'completed' and the "Risk Indicator" to reflect the RYG colors based on the "Age Group" and the "PS Shadow Status".

Red - At Risk

Yellow - Pending

Green - Open

Blue - Completed

I have searched around to see if I could find a formula to use or to mimic, unfortunately, all of the ones I have tried to use are not working. With this being in production/use, I do not want to mess up the data, reports, or the dashboard that are connected to it. In my search I have attempted to use this below formula to grab both of the "Age Group" and the "PS Shadow Status" columns

=IF(AND([PowerStore Shadow Complete]@row = false, [Age Group] = "0-7 days", "Green", IF(AND([PowerStore Shadow Complete]@row = false, [Age Group] = "8-14 days", "Yellow", IF(AND([PowerStore Shadow Complete]@row = false, [Age Group] = "15-20 days", "Red", IF(AND([PowerStore Shadow Complete]@row = false, [Age Group] = "21+ days", "Red", IF(AND([PowerStore Shadow Complete]@row = true, [PS Shadow Status] = "Completed", "Blue"))))

=IF([Age Group]@row = "0-7 days", "Green", IF(AND([PS Shadow Status]@row = "Open", "Green", IF([Age Group]@row = "8-14 days", "Yellow",IF([Age Group]@row = "15-20 days", "Red", IF([PS Shadow Status]@row = "Completed", "Blue")))

I need my end results to look like this:

Any guidance is greatly appreciated. Thank you!

Paul Newcome

Try this...

=IF([PS Shadow Status]@row = "Completed", "Blue", IF([Age Group]@row = "0-7 days", "Green", IF([Age Group]@row = "8-14 days", "Yellow", "Red")))

Paul Newcome

Try this...

=IF([PS Shadow Status]@row = "Completed", "Blue", IF([Age Group]@row = "0-7 days", "Green", IF([Age Group]@row = "8-14 days", "Yellow", "Red")))

Adriane Price

@Paul Newcome - Erma gawsh...it was that simple?! I am seriously 🤦 (palm in the face) right now. Thank you! That worked.

Paul Newcome

Happy to help. 👍️