Formula to clear in certain fields of a row
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?
Best Answer
-
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?
Kind regards
Debbie
Answers
-
Hi Maria,
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, andree@getdone.se)
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
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!
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.
-
Hi Marla
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...
Kind regards
Debbie
-
If a name is in the "Design Change Order requester", then the check in "Status of calculations" will be removed.
-
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?
Kind regards
Debbie
-
Thank you for the information.
-
Happy to help!
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.
-
I agree with @Debbie Sawyer, I think this task would be best accomplished with the use of a custom application / script that would utilize the APIs to make these specific changes. If the grids don't change too much, you could hard code most of the cells to be updated on the change of a given target cell. If cell A1 updates, set A2, A3, A4 to null / empty.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!