Forum Discussion
Help with best practices for how to protect formulas on table and still expand with new records?
I have several tables, one on each worksheet, that need to be able to expand whenever a user types in a new record (formulas/drop-downs copied to new records).
Apparently, Excel doesn't allow protecting the worksheet while also allowing the table to expand as needed. Can anyone suggest the best way to protect the formulas in certain cells from being accidentally overwritten by users when they enter a new record? Again, the table must be able to expand. The only thing I can think of would be to use a set number of records on each table which already contain the cells needing protection, but that comes with its own issues. Any help is appreciated.
I'm using Excel 365. Developing the workbook in the desktop app and then posting to the Internet on a shared drive for users to access and enter data. I can't use VBA or Macros for security reasons.
- You could add a data validation rule to the formula cells. Set it to "Custom" and use this formula:
FALSE
That will disallow any entry to those cells. But pasting on top of them will still be possible.
- You could add a data validation rule to the formula cells. Set it to "Custom" and use this formula:
FALSE
That will disallow any entry to those cells. But pasting on top of them will still be possible.- This is certainly a step in the right direction. Unfortunately, it doesn't keep users from deleting the cell contents altogether. I put a not in the alt text field in data validation saying DO NOT DELETE CELL CONTENTS. Hopefully that will deter them from it. Thank you!
