How do you clear a cell by clicking on a checkbox?

Mark Strecker

Hello,

I have two fields in a given row, "Plates Needed" and "Reason for Reorder", that I want the contents of deleted when someone selects the "Remake Complete" checkbox. Is there a way to do this using a formula and, if so, how would I go about doing that. I've played around with the IF and REPLACE functions, but have had no success.

Andrée Starå

Hi Mark,

Not sure I follow!

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!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Mark Strecker

Andrée

Here is a screenshot of the cells I want to affect:

In the screenshot you can see in the top row that there is information in the "Plates Needed" and "Reason for Reorder" fields. What I want to happen is this: when "Remake Complete" checkbox is turned on, it will erase the contents of the "Plates Needed" and "Reason for Reorder" in the same row. Is that possible?

Thanks!

a

Andrée Starå

Happy to help!

I'm guessing that the information in the Plates Needed and Reason for Reorder columns are added manually. If that is the case, then we would need to either use a third-party service like Zapier to delete the information automatically when the Remake Complete checkbox is checked.

Is that an option?

Or we would need to add additional so-called helper columns to add the information and use the ones you have now to show the information. If that would work depends on your specific process.

What do you think? What would work best?

Mark Strecker

Andrée,

I don't have Zapier and when I went looking to read about what it does, the info I got was so vague I'm still not sure what it can and can't do when connected to Smartsheet. So I guess we'll have to add some extra columns to get the job done if it's possible to do what I want.

Info put into the "Plate Needed" and "Reason For Reorder" fields is indeed put there manually.

Thanks for the help. I appreciate it.

Andrée Starå

Mark,

Zapier is really powerful, but if you can live with some extra so-called helper columns I would recommend to try that first.

Let me know if you have any questions or if you need more help!

✅Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mark Strecker

Andrée,

Yes, I could use help. I couldn't figure out what formula or formulas I needed to clear fields when the checkbox is selected.

Thanks,

Mark

Andrée Starå

In my example below, I'm using two columns for the input (1) and two for the view (2).

The formula will show what's inside the Input fields if the Remake Complete isn't checked and hide it if it is.

Try something like this.

In the Plates Needed (View) column (3)

=IF([Remake Complete]@row = 0; [Plates Needed (Input)]@row; "")

The same version but with the below changes for your and others convenience.

=IF([Remake Complete]@row = 0, [Plates Needed (Input)]@row, "")

In the Reason for Reorder (View) column (4)

=IF([Remake Complete]@row = 0; [Reason for Reorder (Input)]@row; "")

The same version but with the below changes for your and others convenience.

=IF([Remake Complete]@row = 0, [Reason for Reorder (Input)]@row, "")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Make sense?

Would that work?

Mark Strecker

Andrée,

Thanks for taking the time and effort to work on this problem. Alas, just hiding what is in the cells won't work because there is a possibility that different info will need to be entered, and I wanted the cells cleared because many of those using this sheet aren't technically savvy and won't know how to erase a cell. I decided to do this project using Google Sheets for this project instead because I was able to use a script that did what I needed, which is clear the contents of the cells.

Thanks,

Mark

Andrée Starå

Happy to help!

If you want to keep in Smartsheet I would recommend Zapier.

Let me know if I can help!

Mark Strecker

Andrée,

I tried Zapier and it didn't work that well. I also tried it with Google Docs and it did just as poorly there. It had trouble finding samples. Sometimes it did so, but most of the time it didn't. I spend a long time searching for troubleshooting answers via different forums but the support for Zapier was a sparse.

Thanks,

Mark

Andrée Starå

Yes, Zapier can be some work before you get a hang of it. When you do it's really powerful.

Theres not many forums that can compete with this one.

Mark Strecker

Andrée,,

Yes, the Smartsheet Forum is exceptionally good. My company uses Smartsheet for all sorts of things, but I decided that in this case Google Sheets would be better for this project. Another reason, if you're interested, is because I wanted to create three headers at the top of the page (to be frozen) using merged cells, which Smartsheet can't do. Finally, because those using it aren't tech savvy and outside of my department where I can't readily help them, I was wary of having people work in a program where they have to remember to save their work. I've put improvement requests in to Smartsheet to address the saving issue (I think it should be instantaneous like Google Docs and Sheets), but that was a couple years ago and they haven't addressed it. Maybe it's to come.

Ella

Hey Mark, are you aware of the auto-save options in Smartsheet? Each user can enable them so that changes are saved either after X min of inactivity and/or when you leave the sheet/report.

Mark Strecker

Ella,

Yes, but what I want to see is instantaneous saving that eliminates the need for the save button, and instantaneous refreshing so a person immediately sees the changes made by someone else.

Thanks,

Andrée Starå

@Mark Strecker It sounds like Google Sheets is the correct solution, at least at the moment.