Protecting a sheet with a pivot table automatically makes the table inoperable

English
#1

Version: 24.8.3.2 (X86_64) / LibreOffice Community
Build ID: 48a6bac9e7e268aeb4c3483fcf825c94556d9f92

A sheet containing a pivot table automatically activates cell protection in every cell in the table as soon as the table is refreshed.

My question is, how can one protect a part of a sheet containing a pivot table using Sheet Protection without the entire pivot table becoming inoperable?

Steps to reproduce:

  1. open the attached sheet
  2. Sheet2 contains a pivot table referencing the data on Sheet1. Note that Sheet2 contains no protected cells except one.
  3. Refresh the pivot table.
  4. Note that all the pivot table cells are now protected.
  5. If protection is enabled on Sheet2 at this point, the pivot table will no longer function, as it can no longer be refreshed. Any changes to the referenced named range will not be reflected in the pivot table.

Pivot Protection.ods (13.8 KB)

#2

I canā€™t confirm your claims 4. / 5. regarding 3.
If there are misunderstandings you can tell us after a visit to the attached example.
disask115026PivotTableWithSheetProtection.ods (58.9 KB)

#3

I can reproduce (LO 25.2.0.0.beta1+ and 7.6.7), apparently on Refresh pivot table cells are always written with the protected attribute. Donā€™t know whether that is with a reason or would be a bug.

#4

Can you also reproduce the claim ā€œ5.ā€ by the questioner?
I canā€™t - despite the fact that I also tried with 24.8.3.2.
That cells inside the pivot table are locked against direct editing and formatting (in situ / via context menu) was also the case with LibO V 7.5.3.
Editing and formatting of cells not returning ā€˜Data Fieldsā€™ results is and was possible, however, via F2 or >Format>Cells . Refreshing the table does cancel such changes - and always did.
I think thatā€™s reasonable - and it also works this way if the PT-sheet is not protected.
What did I misunderstand??

#5

I think weā€™re talking past each other at some point. Once the pivot table was refreshed and thus its cells have the Protected attribute and then the sheet it resides on is protected, the context menu on a pivot table cell does not offer Refresh anymore (thereā€™s only Copy left), and the submenu under Data ā†’ Pivot Table has only greyed-out options. Also editing such pivot table cells is not possible, apart from hitting F2 and selecting cell content, applying formatting attributes isnā€™t either.

#6

It does for me persistently (in 7.5.3 and in 24.8.2.3 e.g.) . Where may our different observations have a cause?
In the originally attached example strangely Sheet1 is protected, but Sheet2 is not. I protected it with the empty password.
If I then unprotect Sheet1, and change/insert/delete data there, I can successfully refresh the pivot table as often as needed. Also editing of the properties works as expected.

#7

In 24.8.3.2 I am in fact getting this behavior. Maybe I can demonstrate in another way.
I believe the problem takes place when an unprotected sheet containing a pivot table that has been refreshed is subsequently protected.
I recommend turning on Value Highlighting (ctrl-F8) which conveniently shows protected cells in locked sheets.

  • Open file from @Lupp (disask115026PivotTableWithSheetProtection.ods).
  • Unprotect sheet ā€œPivotTable_Sheet1ā€
  • change some data in ā€œSheet 1ā€.
  • Refresh the pivot table to see those changes.
  • Protect ā€œPivotTable_Sheet1ā€ again.
  • You will find that now the pivot table cells are all protected and locked.

Changing the data in Sheet1 will have no effect in the pivot table, because although Refresh is still available in both context menu and Data/PivotTable/Refresh, you will receive the error Protected Cells cannot be modified.

Is this now reproduceable?

#8

Yes, now I see. Oweia! So complicated!
Remedy:

  1. Unprotect the pivot-table sheet.
  2. Select the columns of the pivot-table (or more if you canā€™t be sure to NOT need upsize it by editing the properties).
  3. Make the range explicitly unprotected.
  4. Protect the sheet again.
1 Like
#9

Yes, I think this is a ā€œbugā€ and Iā€™ll file a report.
The undesired behavior is that pivot table cells that are explicitly unprotected will revert to protected if the pivot table is refreshed while the sheet is unprotected, rendering it inoperable when the sheet becomes protected.

Itā€™s a mouthful, butā€¦ thank you for helping me explicitly define the issue.

#10

Please post a link to the bug report in this thread then.

Impressum (Legal Info) | DatenschutzerklƤrung (Privacy Policy) ā€‚ Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.