Microsoft Excel - Allow Edit Ranges
Вставка
- Опубліковано 5 жов 2024
- Learn a more user-friendly way to apply sheet protection, as well as how to protect cell ranges based on a user's login credentials (no passwords required).
00:19 Commonly Used Cell Protection Strategy
01:05 The Problem with Standard Protection
01:16 New Way to Implement Cell Protection
02:39 Updating Password Information
02:57 Username Authentication
04:11 Dealing with Conflicting Permissions
05:46 Delete Protection Ranges
Never heard of "Allowed edit ranges". Super new info, and thank you for all of your tutorials. 👌
Happy to help! So glad you are enjoying them. Thanks for watching.
Outstanding! I just finished the Sheet password video which was also great. I've watched literally over a thousand Excel videos, and aside from never having seen user protection discussed, both videos were short, clear, and to the point. Thank you!
Thanks, Gossamer. I appreciate you taking the time to watch and leave nice comments.
Absolutely brilliant. Solved a current real world problem.
So glad to hear. Sometimes, things just come along at just the right time.
I have recently discovered your channel. I must say yours is one of the better one. Subscriber numbers doesn't justify the quality provided.. Wish you all the best. Keep going. Keep growing.
Thanks so much for your kind words. The channel is still in its infancy (less than 1 year old), but I hope the numbers will continue to increase. Thanks for watching.
Super tip to manage access to delicate files in the company.
Very helpful. Thank you. I have a different but related question about protecting workbooks. The use case is a single workbook with multiple worksheets that aggregate to a summary worksheet. The worksheets could represent data for particular people or teams or departments. In any case, the worksheets are identical in structure. The question is, can I share the entire workbook with all the entities represented by the particular worksheets but only allow each entity to see its particular worksheet. In other words, not just protect the other sheets but also effectively hide them? That would make the document management much easier because additional copies would not have to be created, and subsequently recompiled, in order to achieve the visual security need. Thanks in advance for your help.!
That's a great question. I've put together a quick test file to demonstrate a possible solution. I'm sure there are better ways to do this (as there always are), but it's the first thing that came to mind without making things too complex.
You'll need to go into the Visual Basic Editor to see the related code that is linked to the colored buttons and chameleon (hide) button.
www.bcti.com//wp-content/YT_Downloads/PW_Sheets_Test.zip
The workbook password is "zzz", and the sheet access passwords are the names of the sheets (ex: "North", "South", "East", and "West"). Let me know if there is anything unclear about how this is done.
This would make a great topic for a weekly video 😁. Thanks for watching.
That is the use case that troubles me too
Check out the file link in my original reply to see one way to solve that issue.@@DeepakChauhan-mn5jw
@@bcti-bcti Thank you. You have taught me something fantastic today.
excellent, thank you for sharing
Thank YOU for taking the time to watch.
Protecting some ranges with Username Authentication is really great, thank you fro this tip !
Is there a way to protect the entire sheet(s) the same way, without defining a range ?
Not that I'm aware of.
Wonderful way to setup single platform for a lot of users to update their own stuff while given the view of other people's update too. Just wondering though, despite the cell range being locked as absolute reference, can it also be dynamic if we apply it to official Excel table range when the table expands?
That's a great question. I've not been able to get it to behave dynamically, but if I figure it out, you'll be the first one I tell. Thanks for watching.
Great tip!
I face exterme slowness in powerquery. Every line I select in the query it refreshes all the previous steps. I want BCTI to make a video on this.
What would you want the objective of the video to be?