Hierarchical protection of a workbook
Hi Best Stl!
I have created a large workbook of 10 tabs.
I need three permission levels for people entering/editing data;
Administrator: for myself, full control of everything
Manager: able to enter some budget figures/amend cost prices in a couple of rows/columns that need to be protected from editing by the basic user
Basic User: Daily data entry on limited areas only
If possible I do not want the manager to have to protect a worksheet after they have edited it as I do not think they will always remember to do so.
Can you suggest anything?
Thanks
Karen
Read more: https://www.stl-training.co.uk/post-33589-hierarchical-protection-workbook.html #ixzz2SmtGswsQ
RE: hierarchical protection of a workbook
Hi Karen
Thanks for getting in touch. Excel doesn't really support three tiers of administration in the way you require. It could cope with Admin / Manager or Admin / Basic User but not all three.
Instead I would recommend you set up two separate workbooks. Your first workbook is for Admin & Manager. Use workbook protection with a password for editing (you get to this via File > Save As > Tools > General Options and enter a Password to Modify). This means when the Manager opens the workbook they enter the password that the two of you know, and then they can carry on working. When they close the file they will not need to re-enable security, but they will be prompted for a password next time.
The second workbook will be for the Basic User. They will input data into their book. This data will then be linked via a formula into the Admin & Manager workbook. (If you just need their data replicated - instead of SUMs etc. - then use either VLOOKUPs or Paste Link).
This disconnects the processes but allows the users to perform their own roles.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
