Calculate formula only once.

[smunn]

I have a worksheet that has multiple formulas on it. These formulas
need to update the values as more data is input. However, I have one
formula that I want to only calculate once and then remain as that
value.

Example: =IF(L6="","",NOW())

I want the formula cell to display the time that a value was entered
into L6 and then remain that value.

Thanks,

 

[JE McGimpsey]

Take a look at

http://www.mcgimpsey.com/excel/timestamp.html

 

[Gord Dibben]

smunn

To enter a static date/time in a cell you cannot use the NOW() or TODAY()
functions.

These will always update.

Event code will place the static date/time in a cell if L6 <> ""

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("$L$6")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
Excel.Range("$M$6").Value = Now
Else: Excel.Range("$M$6").Value = ""
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This is event cxode and goes into the worksheet module.

Right-click on the sheet tab and "View Code".

Paste the above into that module.


Gord Dibben Excel MVP