Freeze formula results

[nicoleto]

Is there any way to define a function that would copy the result of on
formula (from one cell) onto another cell and then erase itself so th
copied result would not be recalculated?

I.e:

In cell C1 I would have (=A1+B1). The idea is to put in cell D1
function like

=IF(C1<>"","",Freeze())

And Freeze() would be the defined function that would copy the resul
from cell C1 to cell E1 and then would erase the IF function so n
matter if I change the values in A1 or B1 the value of D1 will stay th
same.

Thanks in advanc

 

[Frank Kabel]

Hi
without using VBA (e.g. processing the worksheet_change event) this is
IMHO not possible. You may the following code (put it in your worksheet
module):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:C1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
If Range("C1").value <>"" and Range ("D1").value = "" then
Application.EnableEvents = False
Range("D1").value = Range("C1").value
end if

CleanUp:
Application.EnableEvents = True
End Sub

 

[Patrick Molloy]

A function cannot set the value on a cell other than the
cell in which the function is called from.

You can use a sub ( macro or procedure is the same thing)
to achieve this. First, here's the sub, which you copy
into a standard module...

SUB SetValue(Source as Range, Target as Range)
If Target.Value="" then
Target.Value = Source.Value
End If
END SUB

The sub checks that the destination(or Target) cell is
empty. If it is, then the value of the source cell is
copied into it.

How can we call the procedure? Well, for your purpose,
you could use the sheet's CHANGE event ... eg copy the
following inh to worksheet's code page ( right click the
sheet tab)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c2:C5")) _
Is Nothing Then

SetValue Target.Offset(0, 1), Target.Offset(0, 2)

End If
End Sub

Now, when you enter a value into any cell in the range
C2:C5, the value from D25 gets copied to the next
column.

Patrick Molloy
Microsoft Excel MVP