Trying to determine where we are in the process

Options

I am trying to show the status of a team based on four steps in the process. I have written it as an IF AND statement but can't figure out how to get past the first check.

step 1 Department review complete, step 2 internal review complete, step 3 user input complete, step 4 build complete step 5 pick up complete

Each step is a column and the input is either yes, or no. In the last column I want the status.

so if step 1 is no, the status should show as Department Review. If step 1 is yes and step 2 is no the status should read "Internal Review" if step 1 and 2 are yes and 3 is no status = User Input and so on...but my brain can't wrap my head around writing this and everything is coming up errors.


Any assistance would be greatly appreciated!

Best Answer

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    Hey @LaB

    You're on the right track. You can use IF and AND to evaluate the columns, but work backwards. Try this one...

    =if(and([Step1]@row = "Yes", [Step2]@row = "Yes", [Step3]@row = "Yes", [Step4]@row = "Yes", [Step5]@row = "Yes"), "Pick Up Complete", if(and([Step1]@row = "Yes", [Step2]@row = "Yes", [Step3]@row = "Yes", [Step4]@row = "Yes"), "Build Complete", if(and([Step1]@row = "Yes", [Step2]@row = "Yes", [Step3]@row = "Yes"), "User Input Complete", if(and([Step1]@row = "Yes", [Step2]@row = "Yes"), "Internal Review Complete", if([Step1]@row = "Yes", "Department Review Complete", "")))))

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @LaB

    You only really need to resolve for the "No" response at first. Something like this would get you started by putting this in your status column:

    =IF([Department Review]@row="No", "Department Review", IF([Internal Review]@row="No", "Internal Review", IF([User Input]@row="No", "User Input", IF([Build Complete]@row="No", "Build Complete", IF([Pick Up]@row="No", "Pick Up"

    After the end of the formula above, you'd put a comma and then what you'd want it to show if everything is a Yes.

    The reason you only have to resolve for the No situations in the formula is because from your explanation it sounds like a Yes response just forwards you to the next step to see if it's a Yes or a No and only your No responses sound like they generate a status type, until the very end when all are a Yes.

  • Julie Fortney
    Julie Fortney Overachievers
    Answer ✓
    Options

    Hi @LaB

    Try this formula:

    =IF([Step 1]@row = "no", "Department Review", IF([Step 2]@row = "no", "Internal Review", IF([Step 3]@row = "no", "User Input Complete")))

    Think of the IF formula this way: if the first argument (Step 1 = "no") is true, Smartsheet will put "Department Review" in the status column. If that argument is false, it moves on to the next IF statement. In this IF formula, you will never again have to evaluate Step 1.

    I hope that helps!

    www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

  • Emilio Wright
    Emilio Wright ✭✭✭✭✭
    Options

    When I have to nest many IFs I prefer writing them on a text editor like VS Code. I will write multiple lines for all of the IFs I need.

    • assuming there is always a default value of yes or no in every column
    • I left the last "" empty because you did not have a value for when step 5 is "yes"

    Something similar to this:


    =IF(Step1@row = "no","Department Review Complete",IF(Step2@row = "no","Internal Review Complete",IF(Step3@row = "no","User Input Complete",IF(Step4@row = "no","Build Complete",IF(Step5@row = "no","Pick Up Complete","")))))
    


    EDIT: After reading your post again I think I understand what you want. I modified the formula to include one more value. Let me know if it works.


    =IF(Step1@row = "no","Department Review",IF(Step2@row = "no","Department Review Complete",IF(Step3@row = "no","Internal Review Complete",IF(Step4@row = "no","User Input Complete",IF(Step5@row = "no","Build Complete","Pick Up Complete")))))
    
  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @LaB

    I like @Julie Fortney 's better. It's cleaner than mine!

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • LaB
    LaB ✭✭
    Options

    @Julie Fortney that was so simplistic and amazing! thank you so much, worked a treat!!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!