Prevent accidential change in formulas

[Thomas5000]

Ok i have several spreedsheets on a server used by various personnel. Is there a way to prevent formulas from being acccidentally changed?

 

[SkipVought]

Hi,

Check out worksheet protection.

Tools/Protection/Protect Sheet...

Skip,

_[red][/red]

 

[BlueHorizon]

Hi,
You can unlock the cells you want users to enter data in (Format|Cells|Protection|Uncheck Lock), which means that all other cells will be protected once the worksheet is protected.

Then you can (optionally) hide the contents of the cells that contain formulas (Format|Cells|Protection|Check Hidden) so that the formula is not open to viewing.

Then, as Skip said, you can protect the worksheet so the locking and/or hiding works (Tools|Protection|Protect Sheet).

HTH,


Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[Thomas5000]

Thanks guys. This works very well! Just what i asked for! Thanks.

One other question. Is there a way to prevent or tell if the sheet has outside links? I have a problem with this when i cut and paste workbooks or worksheets, when i reveiw them later i see them referencing other workbooks. (the sheets should only be referencing internal sheets not other workbooks I have another sheet that does that) When there are tons of formulas obviously a few may be overlooked especially if you are pasting several copies. I need to prevent this from happening. It causes the sheets to become high maintenance and data end up wrong because i forget to change a link or two.

Basically i not only need to prevent users from chaning formulas. I really, really need to keep formulas from changing themselves also. It is best that i go ahead an tackle both of these issues now.

Thanks!

 

[SkipVought]

"... keep formulas from changing themselves ..."

That's scary! That can happen if a reference dissapears. Then you get a #REF!

Check out Edit/Links for EXTERNAL LINKS.

Skip,

_[red][/red]

 

[Thomas5000]

Well actually i probably can't do much about that problem but the problem occurs either when i cut and paste formulas or when i change the file name. Maybe i should create a template.

What i ususally do is create a workbook. Then i change the file name. The file is named after each month so that there is one file for each month. Well even though these files don't reference outside workbooks, for some reason, it tries to. I don't exactly know how or why. I could understand this if there WERE outside references then it should continue to reference them. But soem how it adds references during the copying or during the change in filenames.

Any explanation? Advice?

Thanks Guys!