PDA

View Full Version : Excel. Office xp formulae



veterannz
14-08-2004, 03:04 PM
Can anyone tell me how to protect ONLY the formulaue in an excell workbook.
I wish to be able to "clear contents" of any specific cell into which information has been entered incorrectly, but not to upset the formula contained within that cell. I cant see that it is covered in the tick box options available when protecting a worksheet

Mike
14-08-2004, 03:12 PM
If there is a formula contained within a cell, then there is no data contained within the cell, it'll be pulled from somewhere else. That means that if incorrect information has been entered, it would have to have been entered into another cell altogether.

You can protect certain cells and leave others available for editing etc. if that's what you're wanting?

Mike.

veterannz
14-08-2004, 03:22 PM
For example.
D9 contains the formula =SUM('DPA etc totals'!R3:R242).
if I were to enter the figure 6 into cell D9 accidently and then clear the contents to remove the error it would make the formula within that cell useless.
I want to be able to edit cell D9 while still having the formula protected aginst change

Mike
14-08-2004, 03:28 PM
If there is a formula within a cell, and you enter a figure into that cell, then the formula is lost BEFORE you clear the contents to remove that error. As soon as you enter the number 6 into D9 you lose the formula. If there is a formula in the cell then it needs to pull its values from another cell. If you don't want to ever overwrite the formula, then you need to protect the contents of that cell (therefore you would not have been able to enter the value 6 into D9 to start with).

Is that what you're wanting? If you're wanting to enter values into a cell containing a formula without losing the formula, I'm afraid it cannot be done.

Mike.

wotz
14-08-2004, 05:10 PM
What you are looking for is on the protection tab of the format cells menu. Remove the tick from 'locked' box for any cells you want to be able to edit. In your case you might want to select all cells and do this, then put the tick back on for the cells you want to protect. Then protect the sheet fron the tools menu.