Auto-populate request status based on checked items
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
Comments
-
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, andree@getdone.se)
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you - I will give it a try!
-
Sure thing. Let me know how it goes.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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"))))))))))
-
Glad you were able to get a working solution.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Happy to help!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!