Status update to Full depending on drop down list selection

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:


Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this one...

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

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

  • 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
    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")))

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!