Errors with Status Ball Formula

Options

Appreciate any/all help!

I can’t get past errors: #UNPARSEABLE, #INVALID DATA TYPE, #INCORRECT ARGUMENT SET

I'd like to achieve the symbol results in the "Indicator" column based upon entries in "Client Status" and "RCM Status" columns.

Thank you!

Mary

Tags:

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Mary Farmer,

    The formula @Frank S. gave you is missing the "Client" text for the Client Status checks, which is why you're getting blanks. Here's one with it filled in and shortened to have indicators of the same colour combined with OR functions:

    =IF([Client Status]@row = "Client Pending", "Yellow", IF(AND(OR([Client Status]@row = "Client Approved w/ Changes", [Client Status]@row = "Client Approved"), NOT([RCM Status]@row = "Approved/Activated")), "Green", IF(AND(OR([Client Status]@row = "Client Approved", [Client Status]@row = "Client Approved w/ Changes"), [RCM Status]@row = "Approved/Activated"), "Blue", IF([Client Status]@row = "Client Denied", "Red"))))

    Output:

    Hope this helps, if there are any issues then just post and let's fix them! 😊

Answers

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Options

    Greetings @Mary Farmer,

    Here is a sample formula. I shortened the names a little but this should get you headed in the right direct:

    =IF(AND([Client Status]@row = "Pending", NOT(ISBLANK(RCM@row))), "Yellow", IF(AND([Client Status]@row = "Approved w/ Changes", RCM@row <> "Approved/Activated"), "Green", IF(AND([Client Status]@row = "Approved", RCM@row <> "Approved/Activated"), "Green", IF(AND([Client Status]@row = "Approved w/ Changes", RCM@row = "Approved/Activated"), "Blue", IF(AND([Client Status]@row = "Approved", RCM@row = "Approved/Activated"), "Blue", IF([Client Status]@row = "Denied", "Red", ""))))))

    I hope this helps and have a great weekend.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Mary Farmer
    Mary Farmer ✭✭✭✭✭
    Options

    Hi @Frank S. Thank you for looking at this. I edited "RCM" to [RCM Status] in the formula, then pasted in the formula into the "Indicator" column, which looks impressive, but blank indicators are returned. I added a column "Hoping to see" in the attached/below example for reference. Hoping it will be an easy fix for you & your skills. Appreciate any help. Mary



  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Mary Farmer,

    The formula @Frank S. gave you is missing the "Client" text for the Client Status checks, which is why you're getting blanks. Here's one with it filled in and shortened to have indicators of the same colour combined with OR functions:

    =IF([Client Status]@row = "Client Pending", "Yellow", IF(AND(OR([Client Status]@row = "Client Approved w/ Changes", [Client Status]@row = "Client Approved"), NOT([RCM Status]@row = "Approved/Activated")), "Green", IF(AND(OR([Client Status]@row = "Client Approved", [Client Status]@row = "Client Approved w/ Changes"), [RCM Status]@row = "Approved/Activated"), "Blue", IF([Client Status]@row = "Client Denied", "Red"))))

    Output:

    Hope this helps, if there are any issues then just post and let's fix them! 😊

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Options

    Hi Mary Farmer,

    The “Client” word is missing in the @Frank S. formula. You can try this formula -

    =IF(AND([Client Status]@row = "Client Pending", NOT(ISBLANK([RCM Status]@row))), "Yellow", IF(AND([Client Status]@row = "Client Approved w/ Changes", [RCM Status]@row <> "Approved/Activated"), "Green", IF(AND([Client Status]@row = "Client Approved", [RCM Status]@row <> "Approved/Activated"), "Green", IF(AND([Client Status]@row = "Client Approved w/ Changes", [RCM Status]@row = "Approved/Activated"), "Blue", IF(AND([Client Status]@row = "Client Approved", [RCM Status]@row = "Approved/Activated"), "Blue", IF([Client Status]@row = "Client Denied", "Red", ""))))))

    I hope this helps

    Thank you

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Here's my take on it:

    =IF([Client Status]@row = "Client Denied", "Red", IF([Client Status]@row = "Client Pending", "Yellow", IF([RCM Status]@row = "Approved/ Activated", IF(OR([Client Status]@row = "Client Approved w/ Changes", [Client Status]@row = "Client Approved"), "Blue"), IF(OR([Client Status]@row = "Client Approved w/ Changes", [Client Status]@row = "Client Approved"), "Green"))))

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Options

    Greetings @Mary Farmer,

    As a few have already pointed out, I left out the "Client" text in the check, which explains why you are getting blanks.

    If you update the formula to include "Client" it will work, see below:

    =IF(AND([Client Status]@row = "Client Pending", NOT(ISBLANK(RCM@row))), "Yellow", IF(AND([Client Status]@row = "Client Approved w/ Changes", RCM@row <> "Approved/Activated"), "Green", IF(AND([Client Status]@row = "Client Approved", RCM@row <> "Approved/Activated"), "Green", IF(AND([Client Status]@row = "Client Approved w/ Changes", RCM@row = "Approved/Activated"), "Blue", IF(AND([Client Status]@row = "Client Approved", RCM@row = "Approved/Activated"), "Blue", IF([Client Status]@row = "Client Denied", "Red", ""))))))

    In addition, a few suggested formulas include OR statements, which perform the same function but shorten the formula length.

    Based on all the feedback, we should have a complete solution for you.

    Frank

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Options

    Hello @Mary Farmer,

    I agree with @Paul Newcome. Start with the most critical issues and go from there. The last false statement should be a default that all values can accept (when everything goes correct). In this case, it's "green". Condensing the formula to have 1 location to specify each color also helps, especially in error handling. For example, if you already know what value should be the result and it's not returning the correct value, you will know what part of the function to start analyzing.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @MichaelTCA I actually don't have a "default" set in my formula. If there is an entry that does not fit one of the criteria in the table in the original post, the formula will output a blank.


    I do tend to start with first priority and work my way down from there, but when it is an unknown priority, I just try to keep it as consolidated as possible (such as in this instance).

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Options

    @Paul Newcome Ya it's kind of a bad habit of terminology for programming. If no value is predefined for a variable, a default value will be returned (or usually "null" in most of my cases). 😊

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @MichaelTCA Yeah. In Smartsheet though it is just left blank, so I tend to save myself a few keystrokes wherever I can.

  • Mary Farmer
    Mary Farmer ✭✭✭✭✭
    Options

    Thank you all for your help! @Nick Korna - your solution worked great for me!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!