RYGB formula help

John Kelly
John Kelly ✭✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answer

Answers

  • L_123
    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.

    · Share on FacebookShare on Twitter
  • John Kelly
    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!


    image.png


    · Share on FacebookShare on Twitter
  • L_123
    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.

    · Share on FacebookShare on Twitter
  • John Kelly
    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")))))

    · Share on FacebookShare on Twitter
  • John Kelly
    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"

    · Share on FacebookShare on Twitter
  • John Kelly
    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!