How to keep a formula when a cell is modified

Tiffany White

Hello, I have a bit of an odd situation and I'm hoping that someone might know how to help. We have a Smartsheet that we use when new clients come on board so that our Customer Care team can complete the account setup. 

The new clients are added to the Smartsheet via a form and we have formulas to automatically add "N/A" to certain columns depending on the value of other cells. These columns are set up as check boxes and will appear either with the checkbox so that the staff can check when the task is complete or "N/A" if our staff don't have to perform this task.

It all works great until our customer care reps become too efficient and check off the box in the top row before another addition is made to the sheet. The formula is then erased for all future rows.

Is there a way around this? Is it possible to put the formula in a hidden column and have the results appear in the checkbox column? Any advise is appreciated!

Lauren B

Could you use conditional formatting instead? You could set up a rule to grey/black out "N/A" fields based on form submission responses as a visual indicator to your team members to skip those tasks. Then you wouldn't have to rely on formulas at all.

However, if you need the "N/A" values there for reporting purposes then you could add two locked formula rows to the top of the sheet, and then train your team members to always pull new tasks below those two rows before editing them. Not very elegant, but I've used it before and it worked.

Tiffany White

I like the conditional formatting; simple but effective. Thank you!