Trying to determine where we are in the process

LaB

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!

Julie Fortney

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

Ryan Sides

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

Mike TV

@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

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

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

@LaB

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

LaB

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