Locked cells with Protect Sheet.

[ness]

I have a workbook with multiple sheets. In each sheet I have a range of
cells that contain formulas. In previous versions of Excel I was able to
lock the cells that contained formulas and then proctect the sheet (with or
without password). This then allowed any user to input data into cells that
were not locked without the fear of the formulas being overwritten.

In this new version I am using the same spreadsheet as before, but it is not
allowing me to put in the data unless I un-protect the sheet first. If I do
that, it opens up the rest of the sheet and the fomulas could be over typed.

Any ideas of how I can prevent this?

[Barb Reinhardt]

On an unprotected worksheet, select FORMAT CELLS and choose LOCKED for the
cells you don't want people to edit and UNLOCKED for ones that you want
edited. I believe the default value for all is LOCKED. Then protect the
sheet with TOOLS -> PROTECT SHEET.

"ness" wrote:

> I have a workbook with multiple sheets. In each sheet I have a range of
> cells that contain formulas. In previous versions of Excel I was able to
> lock the cells that contained formulas and then proctect the sheet (with or
> without password). This then allowed any user to input data into cells that
> were not locked without the fear of the formulas being overwritten.
>
> In this new version I am using the same spreadsheet as before, but it is not
> allowing me to put in the data unless I un-protect the sheet first. If I do
> that, it opens up the rest of the sheet and the fomulas could be over typed.
>
> Any ideas of how I can prevent this?

[ness]

Thanks for this, that is exactly how I would normally do it, except I did not
read the pop up box after selecting Protect sheet option. It asks "Allow all
users of this worksheet to:" and then as the default "select locked cells"
and "select unlocked cells" are both ticked. I only want users to be allowed
to select unlocked cells.

I have tried and tested this now and I am happy with the results, as not
only is the formula safe from deletion, but it does not allow the user to
click anywhere on the locked cells. Where as on previous versions it would
have come up with a pop up message saying that you cannot paste here as the
cells are protected, then you would have to click on the ok message. This way
is a lot more tidy!

"Barb Reinhardt" wrote:

> On an unprotected worksheet, select FORMAT CELLS and choose LOCKED for the
> cells you don't want people to edit and UNLOCKED for ones that you want
> edited. I believe the default value for all is LOCKED. Then protect the
> sheet with TOOLS -> PROTECT SHEET.
>
> "ness" wrote:
>
> > I have a workbook with multiple sheets. In each sheet I have a range of
> > cells that contain formulas. In previous versions of Excel I was able to
> > lock the cells that contained formulas and then proctect the sheet (with or
> > without password). This then allowed any user to input data into cells that
> > were not locked without the fear of the formulas being overwritten.
> >
> > In this new version I am using the same spreadsheet as before, but it is not
> > allowing me to put in the data unless I un-protect the sheet first. If I do
> > that, it opens up the rest of the sheet and the fomulas could be over typed.
> >
> > Any ideas of how I can prevent this?