Auto-populate request status based on checked items

dianeh

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

Andrée Starå

Hi Diane,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

dianeh

We are deploying Chromebooks to our entire organization.  There is a multi-step process which includes some information gathering (survey), providing some initial information to start getting them prepared (welcome letter), ordering the device from our vendor, the vendor shipping the device, the user receives the device and so on.  I will also be building a dashboard based on this Sheet.

I'm will share the Sheet with you.  I've checked a number of items and entered the status that should be returned in the Request Status column.

I really appreciate any advice you can offer.

Thank you.

 

Paul Newcome

You could add a row (top or bottom of the sheet depends on where new rows go). Across that row (I will use Row 1 for this example), you can enter the status for each of the checkbox columns in the corresponding columns. So the [New Request] column would have New in row 1, the [Survey Sent] column would have Survey Sent in row 1, so on and so forth. Make sure they are in the order you want them prioritized in so that the first column is overridden by the second column, second by the third, etc. For this example I am assuming the columns are prioritized in the order you have them listed above.

 

You can then use the following formula in your Status column...

.

=RIGHT(SUBSTITUTE(JOIN(COLLECT([New Request]$1:[Device Returned]$1, [New Request]@row:[Device Returned]@row, 1), ","), ",", "!", COUNTIFS([New Request]@row:[Device Returned]@row, 1) - 1), LEN(SUBSTITUTE(JOIN(COLLECT([New Request]$1:[Device Returned]$1, [New Request]@row:[Device Returned]@row, 1), ","), ",", "!", COUNTIFS([New Request]@row:[Device Returned]@row, 1) - 1)) - FIND("!", SUBSTITUTE(JOIN(COLLECT([New Request]$1:[Device Returned]$1, [New Request]@row:[Device Returned]@row, 1), ","), ",", "!", COUNTIFS([New Request]@row:[Device Returned]@row, 1) - 1)))

.

Long story short: This will display whatever text is in Row 1 for the leftmost column that is checked. So if Columns 1, 2, and 7 are checked, it will display the text in Row 1 of Column 7. If Column 9 is checked, it will display the text in Row 1 of Column 9. If you then check the box in column 10 of that same row so that Column 10 is now the leftmost column checked, it will change to display the text from Row 1 of Column 10.

dianeh

Thank you - I will give it a try!

Paul Newcome

Sure thing. Let me know how it goes.

dianeh

Thanks everyone.  Andree was able to get it working using the following formula:

=IF([Device Returned]@row = 1; "Complete"; IF([Initial Follow Up Complete]@row = 1; "Follow Up"; IF([Setup Scheduled]@row = 1; "Setup"; IF([Device Received]@row = 1; "Received"; IF([Device Shipped]@row = 1; "Shipped"; IF([Device Ordered]@row = 1; "Ordered"; IF([Welcome Email Sent]@row = 1; "Welcome Sent"; IF([Survey Complete]@row = 1; "Survey Complete"; IF([Survey Sent to User]@row = 1; "Survey Sent"; IF([New Request]@row = 1; "New Request"))))))))))

 

Paul Newcome

Glad you were able to get a working solution. 

Andrée Starå

Happy to help!

Best,

Andrée