Harvey Ball Automation

Hello, I'm fairly new to smart sheets.. however I am trying to get the Harvey status ball to auto update.

I can get one string to work properly but when I try to add another "IF" function it throws an error.

When a date is entered in the desired columns I want it to auto spin the wheel.

I know i'm missing some logic here, thank you for your help!


Comments

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 06/29/20

    Hi @KC Fife

    Could you perhaps provide details for when you want each version of the ball to show?

    Logic formulas read left-to-right and stop as soon as something matches the criteria. This means that if we fixed the current formula you have, it would only move to the second statement & look at the Turnover Meeting Complete column if the first column (LOI) is not a date. If that first column has a date, it will stay at "Quarter" and never look to the other statement.

    It looks like you're just missing a closing parenthesis after the pink text (after @row, before the comma). This closes off the ISDATE function so it knows what column to look at.

    You could try swapping the statements around:

    =IF(ISDATE([Turnover Meeting Complete (PE)]@row), "Half", IF(ISDATE([LOI (PE)]), "Quarter"))

    This way, your statement will only check the first column if the last one is not a date... working backwards. Does that make sense?

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!