Allow Row edit based on checkbox

Options
Gaurav Chauhan
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

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Options

    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

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Gaurav Chauhan ✭✭✭✭✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    K.Candela.KCS ✭✭✭
    edited 04/02/24
    Options

    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