How can I have a cell calculate only once?
Example:
Equation in cell D7 "=A1+B1"
So if I create this equation in cell D7 yesterday and A1 and B1 both equal
"1" at that time D7 will display a "2".
So today I change cell A1 to a "3". I don't want D7 to change to a "4". I
want to copy the equation in D7 to Cell D8 and I want D8 to show a value of 4
and for D7 to not update so it always shows a value of 2.
So basically the equation only calculates once, when it was created. So
cells D7, D8,.... are giving me a history of the sum of the values from A1
and B1 as they change day to day.
Thanks for the help
Adam

Adam,

Don't use formulas: Use an event procedure to put the value of interest into an appropriate cell.

Copy the macro below, right click on the sheet tab, select "View Code", and paste the code into the
window that appears. When you change either A1 or B1, the new sum will appear in column D at the
bottom, with a date next to it (for record-keeping purposes).

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range

If Target.Cells.Count 1 Then Exit Sub

If Not Intersect(Range("A1:B1"), Target) Is Nothing Then
Application.EnableEvents = False
Set myC = Cells(Rows.Count, 4).End(xlUp)(2)
myC.Value = Range("A1").Value + Range("B1").Value
With myC(1, 2)
.Value = Date
.NumberFormat = "mmm dd, yyyy"
.EntireColumn.AutoFit
End With

Application.EnableEvents = True
End If
End Sub

"adamb" wrote in message
...
How can I have a cell calculate only once?
Example:
Equation in cell D7 "=A1+B1"
So if I create this equation in cell D7 yesterday and A1 and B1 both equal
"1" at that time D7 will display a "2".
So today I change cell A1 to a "3". I don't want D7 to change to a "4". I
want to copy the equation in D7 to Cell D8 and I want D8 to show a value of 4
and for D7 to not update so it always shows a value of 2.
So basically the equation only calculates once, when it was created. So
cells D7, D8,.... are giving me a history of the sum of the values from A1
and B1 as they change day to day.
Thanks for the help
Adam