Lock a Cell Unless Conditions Met

Tim Dollmeyer

I'm trying to keep users from updating a cell until another cell is filled in. I tried using a workflow, but it's not working. Anyone have any experience with this?

Mike Wilday

Have you tried refreshing your sheet after the initial column is filled in? Sometimes automation don't fire until the data is saved. Let me know if that helps or not, and I can help you dig into it more.

Tim Dollmeyer

Thanks for your response!!

Yes, I always save and refresh a few times (too impatient to wait for the system).

Here's how I have it set up:

I have a column called "Tech Status" that is locked. I have two workflows set up: 1) enter a value "Ready for Status" when anything changes in a column called "Tech Diagnosis". 2) Unlock the row "Tech Status" when "Ready for Status" is entered.

The first workflow is working, the unlock one is not.

Mike Wilday

Hmmm. The challenge is that you have to unlock the column not the row. Row unlock locks and unlocks an entire row. You would have to apply that to the entire column. Perhaps a better choice would be to add a formula in the cell so that editors can't edit it. Then use the delete a cell's value workflow.

https://help.smartsheet.com/articles/518312-adding-editing-clearing-cell-content#:~:text=Select%20one%20or%20more%20cells,cell%20and%20select%20Clear%20Contents.

Neil Watson

@Tim Dollmeyer a possible workaround is to use the "Change Cell Value" automation with a condition, that overwrites the "locked cell" with an error message unless the "condition cell" is completed? And second automation to notify if this error message is triggered.

Tim Dollmeyer

Thanks for your help! I set up a workflow that deletes any input in the status cell if the diagnosis cell is empty. Sneaky, but it works.

Mike Wilday

@Tim Dollmeyer Awesome. I am glad I could help. Where there's a will there's a way! #creativeSolution for the win!

Steve Krutsch

I am trying to accomplish the same task. @Mike Wilday you mentioned entering a formula that would not allow them to edit the cell and then putting in the automation that clears the formula once the other cells are filled. What formula could I use that would stop someone from editing it?

Mike Wilday

Hi Steve, you could prepopulate the cell with any formula really... ="N/A" would put the text N/A and then you can use the workflow to clear it once it is needed. You have to make sure that your users aren't admins on the sheet.

Mike Wilday

I might also be thinking of using a report to facilitate the editors management of their cells too. Editors can't edit a cell that has a formula in it.