RYGB formula help

John Kelly

Hi all,

I would appreciate tapping into your vast smartsheet knowledge. I am looking to integrate RYGB balls into a formula but I am having trouble linking them altogether.

My needs are:

1. When [Actual Ordered Date] is blank and [Architect Approval] is not blank highlight Red ball. I have the following formula working for the [Architect Approval] is not blank part but having trouble knitting it altogether =IF(NOT(ISBLANK([Architect Approval]@row)), "Red", "")
2. I have the formula working for Yellow and Green balls. If [Due Date On Site] is blank, do not highlight any ball. If [Due Date On Site] is in the past and [Item Closed] check box is not checked, highlight Yellow ball. If [Due Date On Site] is within the next 6 days and [Item Closed] is not checked, highlight Green ball. =IF(ISBLANK([Date Due On SIte]@row), "", IF(AND([Date Due On SIte]@row < TODAY(), [Item Closed]@row = 0), "Yellow", IF(AND([Date Due On SIte]@row - TODAY() < 6, [Item Closed]@row = 0), "Green")))
3. If [Due Date On Site] is further than 6 days in the future, highlight Blue ball. I haven't got this far as I was pulling my hair out trying to get 1 & 2 to work together.

Thanks

John

L_123

=if(and(not(isblank([architech approval]@row)),isblank([Actual Order Date]@row])),"Red", IF(or(ISBLANK([Date Due On SIte]@row),[item closed]@row =1), "", IF([Date Due On SIte]@row < TODAY(), "Yellow", IF([Date Due On SIte]@row - TODAY() < 6, "Green","Blue"

Missed a parenthesis. My bad

L_123

What is item closed supposed to do? you never return what to do when it is checked, but you add it in your criteria twice.

John Kelly

Item closed is in the formula as once it is checked, the row is finished with. Therefore I don't want to pull that row into my dashboard.

Essentially what I am doing is making a procurement tracking section in my main dashboard.

Red ball indicates that the item is not ordered yet - Actual order date blank and Architect approval not blank, indicates that the item should have been ordered but is not.

Yellow ball indicates that the Date due on site is in the past and is either on site, installed or behind schedule, and needs to be actioned - Date due on site in the past and Item closed not checked.

Green ball indicates that Date due on site is within the next 6 days

Blue ball indicates that Date due on site is more than 6 days in the future - long lead item.

Hope that hasn't made it more confusing!

L_123

=if(and(not(isblank([architech approval]@row)),isblank([Actual Order Date]@row]),"Red", IF(or(ISBLANK([Date Due On SIte]@row),[item closed]@row =1), "", IF([Date Due On SIte]@row < TODAY(), "Yellow", IF([Date Due On SIte]@row - TODAY() < 6, "Green","Blue"

see if that gets you what you want.

John Kelly

Getting #INCORRECT ARGUMENT SET with that.

=IF(AND(NOT(ISBLANK([Architect Approval]@row)), ISBLANK([Actual Date Ordered]@row), "Red", IF(OR(ISBLANK([Date Due On SIte]@row), [Item Closed]@row = 1), "", IF([Date Due On SIte]@row < TODAY(), "Yellow", IF([Date Due On SIte]@row - TODAY() < 6, "Green", "Blue")))))

John Kelly

Seems to be in the first part that the error is occurring as when split out, the rest of the formula is working fine.

=IF(AND(NOT(ISBLANK([Architect Approval]@row)), ISBLANK([Actual Date Ordered]@row), "Red"

L_123

=if(and(not(isblank([architech approval]@row)),isblank([Actual Order Date]@row])),"Red", IF(or(ISBLANK([Date Due On SIte]@row),[item closed]@row =1), "", IF([Date Due On SIte]@row < TODAY(), "Yellow", IF([Date Due On SIte]@row - TODAY() < 6, "Green","Blue"

Missed a parenthesis. My bad

John Kelly

Thanks that helped. I made a small tweak to add another variable.

=IF(AND(NOT(ISBLANK([Architect Approval]@row)), ISBLANK([Actual Date Ordered]@row)), "Red", IF(AND(NOT(ISBLANK([Architect Approval]@row)), ISBLANK([Date Due On SIte]@row)), "Red", IF(OR(ISBLANK([Date Due On SIte]@row), [Item Closed]@row = 1), "", IF([Date Due On SIte]@row < TODAY(), "Yellow", IF([Date Due On SIte]@row - TODAY() < 6, "Green", "Blue"))))

Thanks for your help.