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

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

Does anybody have any other non macro thoughts?

Or can anybody help with the Macro as that isnt something i have done previously?