Protecting your formulas in multiple worksheets [Solved/Closed]

dkayeh1 24 Posts Monday November 2, 2015Registration date December 4, 2015 Last seen - Nov 9, 2015 at 01:53 PM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Nov 9, 2015 at 04:32 PM
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.
See more 

5 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 9, 2015 at 01:59 PM
1
Thank you
one option would be to protect the formula cells only and leave others open for editing
https://support.microsoft.com/en-us/kb/214081

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1671 users this month

dkayeh1 24 Posts Monday November 2, 2015Registration date December 4, 2015 Last seen - Nov 9, 2015 at 03:24 PM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 9, 2015 at 03:34 PM
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 24 Posts Monday November 2, 2015Registration date December 4, 2015 Last seen > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 9, 2015 at 04:05 PM
thank you 2010 version
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > dkayeh1 24 Posts Monday November 2, 2015Registration date December 4, 2015 Last seen - Nov 9, 2015 at 04:32 PM
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