Previous Topic Next Topic

Protecting your formulas in multiple worksheets

Solved/Closed
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 - Nov 9, 2015 at 01:53 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - 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.
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 9, 2015 at 01:59 PM
one option would be to protect the formula cells only and leave others open for editing
https://support.microsoft.com/en-gb/office/lock-or-unlock-specific-areas-of-a-protected-worksheet-75481b72-db8a-4267-8c43-042a5f2cd93a?ui=en-us&rs=en-gb&ad=gb
1
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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 ?
0
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Nov 9, 2015 at 04:05 PM
thank you 2010 version
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015
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
0

Similar discussions

To protect you, your profile is not visible to people on FB
limbu86 -
HelpiOS -

1 response
Facebook says I have been trying too often
Sameer -
owilson -

2 responses
Can’t Log into Instagram from Multiple Devices
Isabella -
Abhishek0025 -

5 responses
Facebook
Kasonde -
ElenaKM -

1 response