Status update to Full depending on drop down list selection

Bethany Johnson

Hi There,

I'm attempting a formula based on the responses of certain calls that come in or calls we make. There are 4 columns total where we are either calling out to somebody and trying to track response, or they are calling back in and we complete the employee. When we call out, we have to call out every 2 days up to 3 attempts. If they answer and we are successful, we mark "Enrollment Complete" and don't need to make any other calls. If they return the call, under Inbound Call Result, we could also mark "Enrollment Complete". My issue is, when I attempt the formula several different ways, it won't recognize that when any of these 4 columns say "Enrollment Complete" the status bar should be "Full". That lets us know we don't need to be calling out, and that they have called back in. How can I get the status bar to update to "Full" automatically when that option is selected? If there is nothing there, it should be "Empty" according to the status bar.

I have tried using an IF(AND statement, and when I add each column to it, it generates the error. This was me trying to group it with another row.

=IF(AND([Inbound Call Result:]@row="Enrollment Complete", IF(AND([1st OBC Result]@row = "Enrollment Complete"), "Full", "Empty")))

Here are the names of my columns for tracking the calls:

Here is the wording for the status bar that I have included in the formula:

Paul Newcome

Try this one...

=IF(OR([Inbound Call Result:]@row = "Enrollment Complete", [1st OBC Result]@row = "Enrollment Complete"), "Full", "Empty")

Paul Newcome

Try this one...

=IF(OR([Inbound Call Result:]@row = "Enrollment Complete", [1st OBC Result]@row = "Enrollment Complete"), "Full", "Empty")

Bethany Johnson

Thanks Paul! That worked.

I also had tried an OR statement, but I think I put too many parentheses in there... This helps me so much!

Paul Newcome

Happy to help. 👍️

Bethany Johnson

Another question.. I have 2 formulas that I'm trying to nest together. Both formulas work separately. How can I combine these 2 together?

I'm basically trying to get it to where it's tracking when it says "Enrollment Complete" and marks it as "Full". I'm also trying to track if any of the outbound calls have been made, that some work has been done.

Outbound Call Progress Formula:

=IF(ISBLANK([Inbound Call Result:]@row) = false, "Full", IF(ISBLANK([3rd OBC Result:]@row) = false, "Full", IF(ISBLANK([2nd OBC Result:]@row) = false, "Half", IF(ISBLANK([1st OBC Result]@row) = false, "Quarter", "Empty"))))

"Enrollment Complete" Formula:

=IF(OR([Inbound Call Result:]@row = "Enrollment Complete", [1st OBC Result]@row = "Enrollment Complete", [2nd OBC Result:]@row = "Enrollment Complete", [3rd OBC Result:]@row = "Enrollment Complete"), "Full", "Empty")

Paul Newcome

@Bethany Johnson Try this...

=IF(OR([Inbound Call Result:]@row = "Enrollment Complete", [1st OBC Result]@row = "Enrollment Complete", [2nd OBC Result:]@row = "Enrollment Complete", [3rd OBC Result:]@row = "Enrollment Complete"), "Full", IF([2nd OBC Result:]@row <> "", "Half", IF([1st OBC Result]@row <> "", "Quarter", "Empty")))

Bethany Johnson

Hey Paul,

That was of great help! I did need an additional step at the end for the 3rd OBC piece, but I was able to add that in easily with your formula. Everything is complete now.

I greatly appreciate your time and assistance 🙂

Paul Newcome

Happy to help. 👍️