Excel Worksheet and Workbook Protection - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 5
Adrianna
Hi Mynda, thanks for your video. I have come across an issue regarding workbook protection, which I hope you could help me out. I have setup an excel file with 6 worksheets for 6 different types of reports. These sheets contains various pivot tables and formulas. All data in these 6 worksheets is linked to another excel file. I need to link my data to this external file because the data is updated constantly, so I want my users to be able to refresh the data when they run a report. I have set Protect worksheet with -Edit Objects- and -Use PivotTable and Pivot Charts- ticked, so that my users can use Slicers and hit Refresh all when they want to run a report. I was able to lock part of my sheets with a password, to allow my users to interact with the slicers and for my pivot tables to be refreshed once my users hit the Refresh all button, but they will not be able to change any other parts of the sheets, as I have set passwords for them. I tested this many times and everything was working as desired, all slicers worked fine and all PivotTables were refreshed seamlessly, UNTIL TODAY, when I hit Data > Refresh all, I received an alert that -This command cannot be performed while a protected sheet contains another PivotTable report based on the same source data. To remove protection from the sheet that has the other report, click the sheet tab, and then click Unprotected Sheet (Review tab, Changes group). Then try the command again-. I-m not so sure what-s happening because with the current alert, it seems like I only have one option to unprotected all my worksheets so that my users can refresh data, and this is exactly what I try to avoid, as I need to ensure no one will accidentally change any parts of the file that were not meant to to changed. I have been checking every single PivotTable and none of them seems to be an issue to me. Hopefully you will be able to help me out. Thanks Mynda heaps, I appreciate it!
reply
Hi Mynda, thanks for your video. I have come across an issue regarding workbook protection, which I hope you could help me out. I have setup an excel file with 6 worksheets for 6 different types of reports. These sheets contains various pivot tables and formulas. All data in these 6 worksheets is linked to another excel file. I need to link my data to this external file because the data is updated constantly, so I want my users to be able to refresh the data when they run a report. I have set Protect worksheet with -Edit Objects- and -Use PivotTable and Pivot Charts- ticked, so that my users can use Slicers and hit Refresh all when they want to run a report. I was able to lock part of my sheets with a password, to allow my users to interact with the slicers and for my pivot tables to be refreshed once my users hit the Refresh all button, but they will not be able to change any other parts of the sheets, as I have set passwords for them. I tested this many times and everything was working as desired, all slicers worked fine and all PivotTables were refreshed seamlessly, UNTIL TODAY, when I hit Data > Refresh all, I received an alert that -This command cannot be performed while a protected sheet contains another PivotTable report based on the same source data. To remove protection from the sheet that has the other report, click the sheet tab, and then click Unprotected Sheet (Review tab, Changes group). Then try the command again-. I-m not so sure what-s happening because with the current alert, it seems like I only have one option to unprotected all my worksheets so that my users can refresh data, and this is exactly what I try to avoid, as I need to ensure no one will accidentally change any parts of the file that were not meant to to changed. I have been checking every single PivotTable and none of them seems to be an issue to me. Hopefully you will be able to help me out. Thanks Mynda heaps, I appreciate it!
reply
YTE
Thank you. This was super helpful. Between this and the first tutorial I watched from you (about creating a dashboard), I have finally organized my company data. Thanks! It is much more soothing listening to your voice than some of the other videos out there. I'm sure you have some up, but videos on how to remove data showing up in pivot tables that shouldn't be there would be great. Somehow I have -(blank)- showing up... I also have a weird thing on dates too. I have a -
reply
Thank you. This was super helpful. Between this and the first tutorial I watched from you (about creating a dashboard), I have finally organized my company data. Thanks! It is much more soothing listening to your voice than some of the other videos out there. I'm sure you have some up, but videos on how to remove data showing up in pivot tables that shouldn't be there would be great. Somehow I have -(blank)- showing up... I also have a weird thing on dates too. I have a -
reply
Emad
Thanks for this video, but actually I still have a serious issue and no answer I can get till now.
Even you protected the worksheet, the slicer right-click menu is still active, and unwanted changes can be made easily from unauthorized persons.
I wish to have a solution on how to disable the slicer right-click and the associated options
reply
Thanks for this video, but actually I still have a serious issue and no answer I can get till now.
Even you protected the worksheet, the slicer right-click menu is still active, and unwanted changes can be made easily from unauthorized persons.
I wish to have a solution on how to disable the slicer right-click and the associated options
reply
STEM
Great video! I am having an issue with a Timeline on my dashboard. The Timeline works great when the worksheet is unprotected but when it is protected, the Year,Quarter,Month,Day is grey and I can't select them. And yes, the Timeline is not locked. Any help would be appreciated!
reply
Great video! I am having an issue with a Timeline on my dashboard. The Timeline works great when the worksheet is unprotected but when it is protected, the Year,Quarter,Month,Day is grey and I can't select them. And yes, the Timeline is not locked. Any help would be appreciated!
reply
Venkata
Great! I rate ----- , This 7.25min video make me not worry to send my Excel Dashboard to customer during development time.
reply
Great! I rate ----- , This 7.25min video make me not worry to send my Excel Dashboard to customer during development time.
reply
Add a review, comment
Other channel videos
