How to protect calculated column in a table from edit but allowing user to add rows

[TonyCrabb]

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

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.
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.


Please let me know where I'm going wrong.

Ta, Tony