Excel : Hiding/Locking Formulae

I have created a spreadsheet that has many formulae. The spreadsheet is to be
used by people who do not have a great understanding of Excel.
I wish to protect the formulae so that others cannot overwrite these cells.
Can anyone tell how to protect the cells without having to protect the whole
workbook.
Thanks. Allan

Unlock all cells except those containing a formula, or unlock cells to be
used for input by users, keep cells with formula locked, then protect the
worksheet (possibly with a password): Tools/Protection/Worksheets

Regards,
Stefi


„Allan” ezt Ã*rta:

> I have created a spreadsheet that has many formulae. The spreadsheet is to be
> used by people who do not have a great understanding of Excel.
> I wish to protect the formulae so that others cannot overwrite these cells.
> Can anyone tell how to protect the cells without having to protect the whole
> workbook.
> Thanks. Allan

One way to lock all those formula cells and unlock all the others...

Select all the cells (ctrl-a (twice in xl2003))
format|Cells|Protection tab|uncheck locked

With all the cells still selected
edit|goto|special|check formulas
then
format|cells|protection tab|check locked

(You may want to lock the cells that have instructions/headers/descriptions,
too.)

Then you have to protect the worksheet
tools|protection|protect sheet

But there are lots of things that can't be done on a protected worksheet. You
may want to test a bit to see if you lose anything you need.


Allan wrote:
>
> I have created a spreadsheet that has many formulae. The spreadsheet is to be
> used by people who do not have a great understanding of Excel.
> I wish to protect the formulae so that others cannot overwrite these cells.
> Can anyone tell how to protect the cells without having to protect the whole
> workbook.
> Thanks. Allan

--

Dave Peterson

Dave

Something I'd like to note. Maybe old news but.........

If the active cell is outside the used range, CTRL + a(once) selects all cells
in sheet.

If inside the used range CTRL + a selects the used range only and CTRL +
a(twice) selects all cells on sheet.


Gord

On Fri, 30 Sep 2005 07:34:51 -0500, Dave Peterson <petersod@verizonXSPAM.net>
wrote:

>Select all the cells (ctrl-a (twice in xl2003))
>format|Cells|Protection tab|uncheck locked

I think it's more of what makes up that current region. If the current region
is less than 2 cells, then all the cells are selected.

I put something in A1:L40, but cleared c11:I25 (just at random).

I selected D17 (empty with empty cells surrounding it)--ctrl-A selected the
whole sheet.

I put something in D17 and with just d17 selected, I hit ctrl-A. I got the
whole sheet.

D17:D18 had data, I got that current region with D17 selected and then ctrl-A.

=====
Now the fun part!

I put something in D17 (still surrounded by empty cells). I selected D17:D18
(d17 the active cell) and hit ctrl-A. I got all the cells.

I selected D16:D17 (d16 active and empty and d17 non-empty). I hit ctrl-a and
the selection never changed.

=====
So this screws up my warning!

I don't like describing the "button" at the top of the row headers and to the
left of the column headers--it's just too many words.

Maybe:
Select A1 and hit ctrl-a twice
it may be overkill, but it always(?) works.

(Ish!)

Gord Dibben wrote:
>
> Dave
>
> Something I'd like to note. Maybe old news but.........
>
> If the active cell is outside the used range, CTRL + a(once) selects all cells
> in sheet.
>
> If inside the used range CTRL + a selects the used range only and CTRL +
> a(twice) selects all cells on sheet.
>
> Gord
>
> On Fri, 30 Sep 2005 07:34:51 -0500, Dave Peterson <petersod@verizonXSPAM.net>
> wrote:
>
> >Select all the cells (ctrl-a (twice in xl2003))
> >format|Cells|Protection tab|uncheck locked

--

Dave Peterson

I agree with statement

>Maybe:
>Select A1 and hit ctrl-a twice
>it may be overkill, but it always(?) works.

The rest of it is a mystery and I'm glad it was you and not myself doing all
that experimenting.

Gord

On Fri, 30 Sep 2005 15:57:32 -0500, Dave Peterson <petersod@verizonXSPAM.net>
wrote:

>I think it's more of what makes up that current region. If the current region
>is less than 2 cells, then all the cells are selected.
>
>I put something in A1:L40, but cleared c11:I25 (just at random).
>
>I selected D17 (empty with empty cells surrounding it)--ctrl-A selected the
>whole sheet.
>
>I put something in D17 and with just d17 selected, I hit ctrl-A. I got the
>whole sheet.
>
>D17:D18 had data, I got that current region with D17 selected and then ctrl-A.
>
>=====
>Now the fun part!
>
>I put something in D17 (still surrounded by empty cells). I selected D17:D18
>(d17 the active cell) and hit ctrl-A. I got all the cells.
>
>I selected D16:D17 (d16 active and empty and d17 non-empty). I hit ctrl-a and
>the selection never changed.
>
>=====
>So this screws up my warning!
>
>I don't like describing the "button" at the top of the row headers and to the
>left of the column headers--it's just too many words.
>
>Maybe:
>Select A1 and hit ctrl-a twice
>it may be overkill, but it always(?) works.
>
>(Ish!)
>
>Gord Dibben wrote:
>>
>> Dave
>>
>> Something I'd like to note. Maybe old news but.........
>>
>> If the active cell is outside the used range, CTRL + a(once) selects all cells
>> in sheet.
>>
>> If inside the used range CTRL + a selects the used range only and CTRL +
>> a(twice) selects all cells on sheet.
>>
>> Gord
>>
>> On Fri, 30 Sep 2005 07:34:51 -0500, Dave Peterson <petersod@verizonXSPAM.net>
>> wrote:
>>
>> >Select all the cells (ctrl-a (twice in xl2003))
>> >format|Cells|Protection tab|uncheck locked

I have now taken off my white coat!

Gord Dibben wrote:
>
<<snipped>>
> The rest of it is a mystery and I'm glad it was you and not myself doing all
> that experimenting.
>
> Gord
>
<<snipped>>