Auto-populate request status based on checked items

dianeh
dianeh ✭✭
edited 12/09/19 in Formulas and Functions

We have a deployment tracking sheet with checkbox columns for each of the following steps.  I want to be able to automate the Request Status column based on which items have been completed (checked)  There are some legitimate situations where some of these steps may be skipped; therefore, the status should be set based on the LAST item in the process that has been completed.

 

For example, if the Device Returned column has a check, the status should be complete no matter which of the other columns have been checked.  Another example: there are checks in the New Request, Survey Complete and Device Ordered Columns. The status should be Ordered. Here is a list of the columns and the associated statuses).  

 

New Request (Status = New)

Survey Sent (Status = Survey Sent)

Survey Complete (Status = Survey Complete)

Welcome Email Sent (Status = Welcome Sent)

Device Ordered (Status = Ordered)

Device Shipped (Status = Shipped)

Device Received (Status = Received)

Setup Scheduled (Status = Setup)

Initial Follow Up Complete (Status = Follow Up)

Device Returned (Status = Complete)

 

I have tried using multiple IF statements listing each in order from New to Device Returned and the other way around.  Help!

 

Thanks so much.

Diane

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!