Difficulties in Refreshing Pivot Tables in Excel 2024 When the Source Table and/or Pivot Tables have Protected Cells

[jfrsanders]

I have a source table including a lot of employee data, and pivot tables on separate tabs that include different summaries of the employee data. The source table has a number of formula cells that I wish to protect from inadvertent editing or deletion, and I also wanted the cells in the pivot tables to be protected from inadvertent editing or deletion. I protected the formula cells in the source table, and it is no problem to add employee data to the source table in spite of the protection. However, when I attempt to do a refresh of all tables (Cntrl+Alt+F5), Excel prevents me from doing this, unless all protection is removed from both the source tables and all of the pivot tables. So this means that when an administrative assistant adds employee data to the source table, he/she is forced to remove the protection from the source table and pivot tables every time new data is added and the pivot tables are refreshed. And then he/she is forced to reprotect every table when the editing is done. This is time-consuming and honestly downright annoying.

Is there any way to get around this problem, i.e., to somehow protect a source table and associated pivot tables during data entry that does not require all protection to be removed to refresh the pivot tables? If anybody has a decent answer to this, I would be very grateful to hear it. Thanks a lot.