Excel 95 (sorry)

['Cia]

My stepdad is using a really old version of Excel. He would like to allow his
church secretary to input data but not delete formulas. (He has his formulas
typed into the same cells she would change). So is there a way to
protect/lock these cells, so that the formulas stay intact? I have read many
posts about locking and protecting, so we understand all that, but doesn't
seem to take care of his problem. He doesn't want to lock the cells, because
then she can't input numbers. Is the only answer to type formulas in a
different cell that references the cell that she types into?

 

[Gord Dibben]

First of all, you cannot have a formula and manual entry in the same cell.

No version of Excel will allow that...........manual entry overwrites the
formula.

You must have a formula that references the input cell or cells.

Secondly..................

I don't think you understand all you need about locking and protecting.

You can lock some cells and leave other unlocked.

By default all cells are locked.

Unlock all cells then select the ones to have locked and format them to
locked.

Now protect the sheet.

I think Excel 95 had that feature but long time since I used Excel 95


Gord Dibben MS Excel MVP

 

[''Cia]

OK. So can you talk to me more about how the formula references the input
cell or cells? Then, I could see how we would lock the formula cells and
unlock the data input cells.

Sorry, its been a long time since I used Excel and I don't remember putting
the formulas in a different cell than the one the number was input to.
Thank you.

 

[Gord Dibben]

Example..........

Column A has numbers in cells A1:A10

Column B has numbers in cells B1:B10

In C1 enter =A1+B1 which sums the numbers in A1 and B1

Copy that down to C10.

Now you can unlock Column A and B but keep Column C locked so's the formulas
cannot be overwritten.

Protect the worksheet............Tools>Protection>Protect Sheet.

You can change the numbers in Columns A and B to get varying results in
Column C.


Gord

 

[Otto Moehrbach]

There are many formulas and there are many variations of those formulas.
What formula you would use would depend on what you want that particular
formula to show. An example is you have 5 in A1 and 6 in B1. In C1 you
would type =A1+B1 if you wanted C1 to show the sum of A1 & B1. HTH Otto

 

[''Cia]

Thank you so much for your help!!
One more thing, is there an easy way for him to move or change all his
formulas to another cell, or should he change the reference numbers in each
formula to separate all of these things?

 

[Gord Dibben]

Moving current formulas could pose a problem depending upon what the
formulas do and how they are constructed.

Perhaps change cell references to Absolute for moving.

i.e. same example as before.

In C1 =A1 + B1

Change it to =$A$1 + $B$1 to make it Absolute instead of Relative.

If you move that formula to D1 it will still point to A1 and B1

An easy way to add the $ signs is to simply select the formula in the
formula bar and cycle through using the F4 key.


Gord

 

[''Cia]

Okay, thank you very much for your help.
As soon as I saw those dollar signs, it all came back to me, from years ago
when I used Excel. That was the missing link that brought all my questions
back to answers... A light bulb moment, I guess.
Now, I will try to help him move those current formulas so that they are
separate from the other cells
I appreciate you taking the time to help us.
'Cia