Calculate formula only once.
 

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,


--
smunn
------------------------------------------------------------------------
smunn's Profile: http://www.excelforum.com/member.php...o&userid=29856
View this thread: http://www.excelforum.com/showthread...hreadid=495576

Calculate formula only once.
 

Take a look at

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

In article ,
smunn wrote:

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,

Calculate formula only once.
 

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

On Thu, 22 Dec 2005 12:20:33 -0600, smunn
wrote:


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,