Allow Row edit based on checkbox

Gaurav Chauhan

Hi Team,

Can you please check and suggest on the functionality. The goal is that the subcategory row are locked and only when the user check boxes under the Applicable column, they are then able to enter the details under the corresponding columns of KPI Goal and KPI Achieved.

If the user does not check box under applicable, the row is locked for making any changes.

Let me know if you have any questions.

Thanks! Gaurav

Paul Newcome

If the row is locked, your users will not be able to check or uncheck a box. You would need to have a separate sheet that has the same rows but with only the checkboxes and leave everything unlocked.

Then in your working sheet with the KPI columns you would use a formula with cross sheet references to pull in the status of the checkbox from the first sheet and set up a lock row and unlock row automation based on the checkbox.

Kaveri Vipat

Hi Gaurav Chauhan,

Are you filling the information with the form and what type of permissions your team members will have?

Please share some more details about it.

Thank you

Kaveri Vipat, Smartsheet Engineer, Ignatiuz Software

Gaurav Chauhan

Hi Kaveri

This would be into the basesheet or the report. But not a form.

What I did was that I have locked the columns KPI Goal and KPI Achieved and created an automation WD wherein when the check box is ticked, the row is make editable for change.

Is there any other options I can look at .

Thanks!

Paul Newcome

If the row is locked, your users will not be able to check or uncheck a box. You would need to have a separate sheet that has the same rows but with only the checkboxes and leave everything unlocked.

Then in your working sheet with the KPI columns you would use a formula with cross sheet references to pull in the status of the checkbox from the first sheet and set up a lock row and unlock row automation based on the checkbox.

Gaurav Chauhan

Thank you. So the option of Checkbox wont work for the Users then. I understand.

Paul Newcome

It won't work if the boxes are on the same sheet.

Gaurav Chauhan

Hi @Paul Newcome - Will the option work if instead for locking the row, we just lock the two columns - KPI Goal and KPI Achieved.

Then build an Automation wherein if the User hits the checkbox under applicable column, the 2 adjacent cells become unlocked for entering details.

If that feasible.

Paul Newcome

@Gaurav Chauhan Automations only lock/unlock rows. You cannot specify which columns to lock/unlock, and if a column is locked then the automation will not make a difference.

K.Candela.KCS

I have solved this successfully by using a summary field. The summary field is called "Row Lock Status" and is a drop down with two options: lock/unlock. I have a "Row Lock Status" column hidden in the sheet that is a column formula: =[Row Lock Status]#. I then set up an automation to lock or unlock rows based on this column's value. It does take a moment to change, but an editor without a license can then lock/unlock rows by changing this summary field value.

This is most useful where you want to allow the users the ability to protect cells from accidental changes. In my case I wanted to protect sum formulas in parent rows, so I also have a "Chl" column which =COUNT(CHILDREN()), and my Row Lock Status column actually reads: IF(Chl@row>0, [Row Lock Status]#,""). This enables the sheet owner to allow people to modify ownership of parent rows, but then lock them to prevent accidental overwrites of formulas.

Kristine Candela, Principal Candela Solutions,LLC