Freeze formula results
 

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

--
Message posted from http://www.ExcelForum.com

Freeze formula results
 

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



--
Regards
Frank Kabel
Frankfurt, Germany

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

I.e:

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

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

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

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

Freeze formula results
 

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 D2:D5 gets copied to the next
column.

Patrick Molloy
Microsoft Excel MVP



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

I.e:

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

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

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

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

.