Clearing data but not formula

[John]

How can I configure cells in Excel 2000 so that when a
user deletes the data in a cell the formula is not
deleted?
John

 

[macropod]

Hi john,

When you delete the contents of a cell with a formula, you delete the
formula, not just the results. To delete the data, you must delete it only
from the cells being referenced by the formula.

Cheers

 

[John]

Thanks for that but:
I have therefore three cells. A1 has data only, B1 has
the formula but from what you say it seems that I need to
put the result in C1 and hide B1 to protect the formula.
My formula =IF(A1="","",IF(OR(A1="X",A1="Y"),"",A1)) What
change do I need to make to the formula to put the result
in cell C1 and how does the user refresh C1 if he deletes
the data?
JOhn

 

[Frank Kabel]

Hi
not possible with formulas. Formulas can't fill other cells

 

[Gord Dibben]

John

No need for a third cell(C1)

If user deletes the data in A1 the formula will still remain in B1.

If you wish to "lock" B1 so user cannot delete the formula, you will have to
protect the worksheet.

By default all cells in a worksheet are locked if Tools>Protection>Protect
Sheet is enabled.

You can select all cells by hitting CRTL + A then go to
Format>Cells>Protection and uncheck "Locked" and OK out.

Go back to the sheet and select just the cells you wish to be locked for
editing then Format>Cells>Protection. Check "Locked" and OK out.

Now Tools>Protection>Protect Sheet.

You can set a password to unprotect if your users are not too sophisticated.

There are password crackers out there easily obtainable, so do not rely on
that to protect really valuable data.

Gord Dibben Excel MVP