Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Status Balls formula help

    ✭✭
    edited 12/09/19 in Formulas and Functions

    Hi,

    I am very new to this an am getting a bit frustrated.  I have the following columns, Ship Date, Fabric Order Date, Fabric ETA, and Fabric Received or In Stock.  The first 3 columns are date columns and the 3rd is a checkbox.

    I would like my status balls to due the following:

    If Fabric Order Date is blank, I would like a blue ball

    If the Fabric ETA has a date 3 days before the Ship Date or closer, I would like a red ball, otherwise I would like a yellow ball.

    If the Fabric Received or In Stock box is checked, I would like a green ball.

    Is this possible to do?

    This is what I have come up with so far.  

    =IF([Fabric ETA]1 + 3 <= [Ship Date]1, IF([Fabric Order Date]1 = "", "Blue", "Yellow"), "Red")

    But it does not make my ball turn green if the Fabric Received or In Stock is checked. --Sometimes a fabric is already in stock and that would be checked first.

    Thank you much for any help that can be offered.

    Thanks,

    Dana

     

     

     

     

     

    status ball.jpg

    Tags:
    · Share on FacebookShare on Twitter

    Comments

    • ✭✭

      I got it to work using this....

      =IF([Fabric Received or In Stock]1 <> 1, IF(OR([Ship Date]1 = "", [Fabric ETA]1 + 3 <= [Ship Date]1), IF([Fabric Order Date]1 = "", "Blue", "Yellow"), "Red"), "Green")

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Well done! Kind of moot now, but this is how I would have done it:

      =IF(ISBLANK([Fabric Order Date]1), "Blue", IF([Fabric Received or In Stock]1, "Green", IF(OR([Fabric ETA]1 +3 <= [Ship Date]1, ISBLANK([Ship Date]1)), "Red", "Yellow")))

      I tend to find that writing IF statements that meet your list of requirements (rather than what they aren't) is easier to manage.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions