Double protecting Excel
Hey Guys, Tina Here. I'm at home working for a small business and they've sent me on a task that I'm stuck on. They have a workbook timecard with everyone on it with protected cells, each employee has their own sheet. They have password protected the formulas for each one, but want to password protect each sheet individually so when someone wants to set their time, they can sign on and do it, but have a different password for the next guy... I know this may sound confusing, because it is, my hope was to find someone on here that has had more excel experience then I. Anything helps. Thanks
Bookmark/Search this post with:
Re: Double protecting Excel
Given the way you have described your requirements. I might suggest that you create a workbook for each person and protect it with their own passwood. Then have links from those work books go to a "master" workbook that is also password protected.
I tried this by creating three workbooks, A, B, and C. On sheet 1 of books A and B I entered "hours" then put a number below it. I then copied the cell and went to book C where I did a right click, then "paste special." I then chose the option of "paste link." All three books were password protected. As I open and change the values in books A and B. I see the changes in book C. It's not pretty but it might make the boss happy.
I will say that if you have a bunch of books to link to, you will find that the links are pretty easy to write yourself. In book C, hit [ctrl]~ (the control and tilde keys together) and the wook book will show the code that links the cells in books A, B. You will find that the reference looks something like =[BookA.xlsx]Sheet1!$A$2, and =[BookB.xlsx]Sheet1!$A$2
Once you see the pattern, you will find that a naming convention like DoeJohn.xlsx will tell you to make a link in the master book called "=[DoeJohn.xlsx]Sheet1!$A$2 " and you wont need to to the right click buiness anymore.
Oh, one more thing, hit [ctrl]~ one more time to make the workbook go from showing the code back to just showing the values you need.
hope this helps!
Adam
p.s. oops! I see that you want just one work book. I was assuming you didn't want employees free to read/manipulate eachother's timesheets. I find that in Excel 2007 you can protect individual sheets. Maybe you could passwork protect each sheet and hoave the links to a passwork protected master page. The process woudl be the same. The links would only differ in that it would be "=DoeJohn!$A$2" would link a sheet named "DoeJohn" to the master sheet.
best of luck.
Re: Double protecting Excel
My Two Cents, and this probably does not help you solve your problem but I feel I must say it since you are using Microsoft Excel and you are posting questions to a LUG.
First of all, password protection on Excel is easily compromised. Secondly, there is no way you can audit these excel spread sheets so if they were compromised, you would never really know. Thirdly, and lastly, this system fails on many different levels for a small business, including embracing highly proprietary software to store and retrieve information which adds not only to the cost of doing business, but starts down the dreadful path of vendor lock in and bad business practices. I hope your employees are honest?
I would offer a new solution, which would include creating a database driven application where folks can enter their time and generate the Excel timesheets on request. There are tons of perl modules out there or even the .net framework can do this.
But to provide you with a short answer, no, there is no way to do this in Excel from what I know. You will have to maintain individual Excel sheets. Excel was never built to be utilized in the way that you are trying to use it. However, you may be able to hack up something using VBA or .net. But you run into problems of version compatibility. Say employee roger has a timesheet and uses Office 2007 and Erica still uses Office 2003. You are going to have make sure the business standardizes on a version of office. Then you still have upgrade issues to deal with. In my opinion, this is a terrible idea in general