Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking cell to protect the formula | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
locking formula cell references | Excel Worksheet Functions | |||
Locking A Formula In A Cell | Excel Worksheet Functions | |||
locking information | Excel Worksheet Functions |