Locking formula but delete cell information
 

I need to be able to delete a number from a cell that ALSO has a formula or
function.
I searched but didn't find what I need. This is the drill...
1.) Example: =IF(B10=127,"$16.00","")
2.) Someone ACCIDENTLY enters XX into the cell
3.) UGHH. They hit delete
4.) =IF(B10=127,"$16.00","") goes BYE, BYE!!

How do I LOCK the formula, but have access to the SAME cell.

Locking formula but delete cell information
 

To protect the cell from users putting info in,highlight all cells that you
want to protect and then goto
Format / Cells/ Protection and check the locked box.

Now the usr can't input into those cells, but the formula still works.

HTH
Michael M

"Nevada1" wrote:

I need to be able to delete a number from a cell that ALSO has a formula or
function.
I searched but didn't find what I need. This is the drill...
1.) Example: =IF(B10=127,"$16.00","")
2.) Someone ACCIDENTLY enters XX into the cell
3.) UGHH. They hit delete
4.) =IF(B10=127,"$16.00","") goes BYE, BYE!!

How do I LOCK the formula, but have access to the SAME cell.

Locking formula but delete cell information
 

You can lock the cell so's users cannot overwrite it using sheet protection.

But what do you mean by having access to the cell?

Can't have both options.


Gord Dibben MS Excel MVP

On Tue, 16 Oct 2007 15:50:01 -0700, Nevada1
wrote:

I need to be able to delete a number from a cell that ALSO has a formula or
function.
I searched but didn't find what I need. This is the drill...
1.) Example: =IF(B10=127,"$16.00","")
2.) Someone ACCIDENTLY enters XX into the cell
3.) UGHH. They hit delete
4.) =IF(B10=127,"$16.00","") goes BYE, BYE!!

How do I LOCK the formula, but have access to the SAME cell.

Locking formula but delete cell information
 

By "Access", I mean routinely plunking data into a cell that ALSO has a
formula.

I'm starting to get the message that a cell that gets data dropped into it
must REFERANCE another cell that has the formula to get a result. Am I on the
right trak here?? One cell, one function...

"Gord Dibben" wrote:

You can lock the cell so's users cannot overwrite it using sheet protection.

But what do you mean by having access to the cell?

Can't have both options.


Gord Dibben MS Excel MVP

On Tue, 16 Oct 2007 15:50:01 -0700, Nevada1
wrote:

I need to be able to delete a number from a cell that ALSO has a formula or
function.
I searched but didn't find what I need. This is the drill...
1.) Example: =IF(B10=127,"$16.00","")
2.) Someone ACCIDENTLY enters XX into the cell
3.) UGHH. They hit delete
4.) =IF(B10=127,"$16.00","") goes BYE, BYE!!

How do I LOCK the formula, but have access to the SAME cell.

Locking formula but delete cell information
 

I think you're on the right lines. A cell can contain either a formula or a
constant, but not both.

In the example you gave, the whole of this expression is the formula:
=IF(B10=127,"$16.00","")

Presumably, what you want access to is just the "$16.00". If so, you need to
rewrite the formula as (say)
=IF(B10=127,C20,"")
The cell containing this formula can then be locked.

C20 will contain "$16.00" and can be left unlocked.

BTW, enclosing things in quotes (such as "$16.00") makes them text strings.
I would usually leave these as numbers, so your formula would be
=IF(B10=127,C20,0)
But that's up to you.

Hope this helps.
Stephen

"Nevada1" wrote in message
...
By "Access", I mean routinely plunking data into a cell that ALSO has a
formula.

I'm starting to get the message that a cell that gets data dropped into it
must REFERANCE another cell that has the formula to get a result. Am I on
the
right trak here?? One cell, one function...

"Gord Dibben" wrote:

You can lock the cell so's users cannot overwrite it using sheet
protection.

But what do you mean by having access to the cell?

Can't have both options.


Gord Dibben MS Excel MVP

On Tue, 16 Oct 2007 15:50:01 -0700, Nevada1

wrote:

I need to be able to delete a number from a cell that ALSO has a formula
or
function.
I searched but didn't find what I need. This is the drill...
1.) Example: =IF(B10=127,"$16.00","")
2.) Someone ACCIDENTLY enters XX into the cell
3.) UGHH. They hit delete
4.) =IF(B10=127,"$16.00","") goes BYE, BYE!!

How do I LOCK the formula, but have access to the SAME cell.

Locking formula but delete cell information
 

THANKS!!! This is starting to make sense...

Paul...

"Stephen" wrote:

I think you're on the right lines. A cell can contain either a formula or a
constant, but not both.

In the example you gave, the whole of this expression is the formula:
=IF(B10=127,"$16.00","")

Presumably, what you want access to is just the "$16.00". If so, you need to
rewrite the formula as (say)
=IF(B10=127,C20,"")
The cell containing this formula can then be locked.

C20 will contain "$16.00" and can be left unlocked.

BTW, enclosing things in quotes (such as "$16.00") makes them text strings.
I would usually leave these as numbers, so your formula would be
=IF(B10=127,C20,0)
But that's up to you.

Hope this helps.
Stephen

"Nevada1" wrote in message
...
By "Access", I mean routinely plunking data into a cell that ALSO has a
formula.

I'm starting to get the message that a cell that gets data dropped into it
must REFERANCE another cell that has the formula to get a result. Am I on
the
right trak here?? One cell, one function...

"Gord Dibben" wrote:

You can lock the cell so's users cannot overwrite it using sheet
protection.

But what do you mean by having access to the cell?

Can't have both options.


Gord Dibben MS Excel MVP

On Tue, 16 Oct 2007 15:50:01 -0700, Nevada1

wrote:

I need to be able to delete a number from a cell that ALSO has a formula
or
function.
I searched but didn't find what I need. This is the drill...
1.) Example: =IF(B10=127,"$16.00","")
2.) Someone ACCIDENTLY enters XX into the cell
3.) UGHH. They hit delete
4.) =IF(B10=127,"$16.00","") goes BYE, BYE!!

How do I LOCK the formula, but have access to the SAME cell.