Protecting your formulas in multiple worksheets [Solved/Closed]

Report
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
-
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Hello everyone, thank you in advance for any help I receive with is question.

I have multiple worksheets in a workbook that have formulas in them but I have different people entering data into them. So what I need to know is there a way to allow anyone to input data but not rewrite over any formulas in that cell (by accident)?

I know you can protect the worksheet but they cannot input data without having the password and then they could"accidentally" erase the formulas (they are some pretty long nested IF statements, so anyone working in excel knows they do not want to keep rewriting them because of a user input error).

Thanks for your help.

1 reply

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
one option would be to protect the formula cells only and leave others open for editing
https://support.microsoft.com/en-us/kb/214081
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5183 users have said thank you to us this month

dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015

Thank you for your help rizvisa1. I followed the steps it said and it does not work I can go in that cell and just type a number and it over-writes the formula in that cell.

I have about 100 rows that I need protected that have formulas in them and the rest of the rows which is about 500 that data is manually entered by multiple users. Is there any other way to do this besides the link you provided which didn't work.

I tried it both ways to highlight the row that I wanted unprotected and the ones I want protected neither way worked. Unless there is a step I am missing.

Thank you for your help.
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
most common oversight is applying protect sheet option. Even if you lock cell, unless you protect sheet, lock/unlock in useless.
what version of excel ?
dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
> rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016

thank you 2010 version
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756 > dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015

This is now I would do
1. select all cells and then right click and choose format Cells
2. Go to Protection and uncheck locked
3.Then enter the formulas where i need to enter and choose to lock that cell
4. once all done, I would go to Review and choose protect sheet and ensure that check box for proctect worksheet and contents of locked cells is checked