Forum Discussion

TonyCrabb's avatar
TonyCrabb
Copper Contributor
Apr 29, 2022

How can i protect a calculated column in a table from being edited but allowing users to add rows?

Hi, I have a simple example of a data entry form (on an excel table) with 4 columns for entry of quarterly financial data. i have a 5th column that adds up the 4 quarters into an annual cost per line. this is a calculated column in the table so adding rows automatically adds the calculation to sum up the annual costs.

 

I need to be able to lock the annual column to stop editing while allowing new rows to be added. This is because i need to distribute the entry across a number of users and need to ensure they dont edit calculated columns.

 

When i protect the column and lock the sheet, i get an error adding rows saying i need to unlock.

 

Any advice on what im doing wrong please?

excel

18 Replies

    • TonyCrabb's avatar
      TonyCrabb
      Copper Contributor
      May 02, 2022

      Some additional context....
      Windows 10 PC
      MIcrosoft 365 Apps for Enterprise
      Excel Version: Excel for Microsoft 365 MSO (16.0.13127.21886) 32-bit

      In the attached file, i have a simple table with one calculated column.

      I have unprotected the date entry columns (title, Q1-Q4 and left the annual caculated column locked.

      I have then turned on sheet protection.

       

      What I want to happen:

      - a user can add lines, delete lines, re-sort lines with the calculated column being locked and automatically added when new lines are added.

       

      What happens:

      - i get an error saying the sheet needs to be unlocked.

       

      It is currently protected so if you try to add a row you will see the error. If you unprotect the sheet (no password) you can add and delete rows fine but can edit the annual total which is what i need to avoid.

       

      Any help appreciated.

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        May 03, 2022

        TonyCrabb 

        Here is a solution with data validation.

        This presented procedure protects the cells from accidentally overwriting the cells.

        However, the locked cell can still be deleted with the Delete key.

        Protected cells can also be overwritten using the copy and paste function.

        The function presented does not represent particularly good protection,

        but it is sufficient in practice in many cases.

        Otherwise it would have to be done with VBA, but it takes time,

        which I unfortunately don't have available.

         

        Hope I was able to help you

         

        NikolinoDE

        I know I don't know anything (Socrates)

Resources