adil

You can store the values from A1 via the change event. This will store them
as you change them. As far as I know, there is no way to retrieve previous
values of a cell once it's been changed. Maybe with some auditing feature,
but I don't know. The change event macro might look like this

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
Target.Offset(0, 1).Insert xlShiftDown
Target.Offset(0, 1).Value = Target.Value
End If

Application.EnableEvents = True

End Sub
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"adil" wrote in message
.com...
Hi;
Is there a way to collect previous values of a cell? I can get the last
value by using circular reference, but would like to collect these values.
for eg; if cell A1 has the following occurences of values
2,3,3,4,5,7,8,8,9,9
in colum B i would like to collect these values as 9,9,8,8,7,5,4,3,3,2
arranged by latest values.

Any help is appreciated

adil