excel - Formula to use current value of a cell, which won't update in the future -

is there function use current value of cell, won't update in future if change value of target cell?

for example: a1 = 5

a3 = a1 (which equals 5)

now, when change value of a1 6 a4 = a1 (should equals 6, a3 should remain 5)

is possible in excel?

say start a1 , a4 both empty. following event macro allow enter value in a1 , have appear in a4. each time value in a1 changed, new value copied first empty cell below a4.

enter following event macro in worksheet code area:

private sub worksheet_change(byval target range)     dim a1 range, a4 range, n long     set a1 = range("a1")     set a4 = range("a4")     if intersect(a1, target) nothing exit sub     application.enableevents = false         if a4.value = ""             a4.value = a1.value         else             n = cells(rows.count, "a").end(xlup).row + 1             cells(n, "a").value = a1.value         end if     application.enableevents = true end sub 

because worksheet code, easy install , automatic use:

1. right-click tab name near bottom of excel window
2. select view code - brings vbe window
3. paste stuff in , close vbe window

if have concerns, first try on trial worksheet.

if save workbook, macro saved it. if using version of excel later 2003, must save file .xlsm rather .xlsx

to remove macro:

1. bring vbe windows above
2. clear code out
3. close vbe window

to learn more macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

to learn more event macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

macros must enabled work!