I have a smartsheet for a workflow. If at some point there is a change order, then the workflow needs to start back at the beginning and most of the populated cells need to be cleared out. Is there a formula to make this automatically happen?
It is a decent requirement but I don't know of a way of setting cell b's value based on the condition meeting true in cell a. I could do it in MS Access back in the day - but I don't think you can do this in Smartsheet. The cell that is being set (i.e. the checkbox clearing) needs to be the cell that contains the formula and if someone puts a tick in the box prior to the need for it to be cleared, the tick will overwrite the formula to take it out again.
You can format a cell based on another cells value, but not change the value of a cell based on another cells value.
As mentioned above this could be achieved in an API or using Zapier potentially...
Maybe you could request this from Smartsheet as a product enhancement?
The third-party service, Zapier, is an excellent option for this scenario. Is that an option for you?
There might be a way to structure it without a third-party service.
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])
I hope that helps!
Be safe and have a fantastic weekend!
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Along with Andree's excellent response about Zapier, I too have an alternative response from a colleague that is:
"You'd need a bit of bespoke API work that will clear out the relevant cells in each sheet. So that the API can identify what needs to be cleared out, the best way would be to design the sheets so that they have one common ID that exists throughout each of the rows in the workflow, and also to define somewhere the column names (or column IDs) associated with these cells in each sheet. An API solution could then be written to clear out each cell in the workflow when triggered."
My colleague said he would probably write his own API for this requirement as it would give a greater flexibility in the functionality required. Food for thought...
If a name is in the "Design Change Order requester", then the check in "Status of calculations" will be removed.
Thank you for the information.
Happy to help!