Errors with Status Ball Formula

Mary Farmer

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

Nick Korna

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! 😊

Frank S.

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.

Mary Farmer

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

Indicator Formula Help 0715.JPG

Nick Korna

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

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

Paul Newcome

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.

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

MichaelTCA

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

@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

@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

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

Mary Farmer

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