Forum Discussion
How can i protect a calculated column in a table from being edited but allowing users to add rows?
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.
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
I know I don't know anything (Socrates)
- I am looking for something more robust than that.
Is there really no inbuilt way in Excel to be able to instert a row into a table while protecting a calculated column? That seems such a basic need?- I don't know of any other approach without VBA, but this doesn't have to be the case, it may be, I don't know, that there could be other ways, but these are beyond my knowledge.