Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
sh-boom
 
Posts: n/a
Default how prevent formula in cell from deleting when deleting value????

example: =sum(a1-b1-c1) answer and formula in cell d1
if i delete the answer to this formula in cell d1 it also deletes
the formula. i want the formula to stay in the cell d1
how can formula be locked or protected in cell d1 and still make
and delete value entries in cell d1 ?????
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

Your message isn't very clear. If you want the formula to remain in D1,
then it will continue to calculate.

If you mean that you want to override the formula, but have it return if
you delete the value used to override it, that will require an event
macro. One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If .Address(False, False) = "D1" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=A1-B1-C1"
Application.EnableEvents = True
End If
End If
End With
End Sub

(Note that the SUM() in your formula is superfluous).

In article ,
"sh-boom" wrote:

example: =sum(a1-b1-c1) answer and formula in cell d1
if i delete the answer to this formula in cell d1 it also deletes
the formula. i want the formula to stay in the cell d1
how can formula be locked or protected in cell d1 and still make
and delete value entries in cell d1 ?????

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding a formula in a cell but when cell = 0 cell is blank Mike T Excel Worksheet Functions 5 May 31st 05 01:08 AM
Creating a check mark box MarthaSue Setting up and Configuration of Excel 18 April 28th 05 12:31 AM
need help creating formula based on cell value Brad Excel Discussion (Misc queries) 3 April 1st 05 07:51 PM
How do I prevent incorrect formula results appearing in cell? Marc Todd Excel Worksheet Functions 2 January 26th 05 08:57 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 11:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"