Verified answer

Depending on how the formula is being periodically refreshed there would be different approaches to solving this problem. I recreated your conditions using a connection to both Access and SQL Server databases. Whenever the dataset refreshed a Worksheet_Change event fired so I decided I would use that as the basis for my solution. If you have a different method for refreshing the data in your cell that does not fire the Worksheet_Change event then you will need to change this solution appropriately. My first attempt requires the cell that continuously updates to be cell B1 so that I don't have to try to find it or keep track of it. Whenever cell B1 refreshes the code checks to see if the value is new and if so it places it in the first blank cell it finds in column A. To make that work do this:

Right-click on the sheet tab and choose View Code to open up the VB Editor. Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim rngSrc As Range

  Dim rngDest As Range

  Set rngSrc = Range("B1")

  Set rngDest = Range("A1")

  If Not Intersect(rngSrc, Target) Is Nothing Then

    Set rngDest = rngDest.End(xlDown)

    If rngDest.Value = "" Then

      Set rngDest = rngDest.End(xlUp)

    End If

    With rngDest

      If .Value <> "" Then

        If .Value <> rngSrc.Value Then .Offset(1).Value = rngSrc.Value

      Else

        .Value = rngSrc.Value

      End If

    End With

  End If

End Sub

Note that the rngDest and rngSrc ranges must not overlap in order to avoid infinite loops and system crashes.

The second version Assumes that your updating cell is the last non-blank cell in column A. If a new value appears when the cell refreshes then a cell is inserted above it moving the updating cell down one row. To make this work right-click on the sheet tab and choose View Code then paste this code:

Dim strOldValue As String

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim rngSrc As Range

  Set rngSrc = Range("A1")

  Set rngSrc = rngSrc.End(xlDown)

  If rngSrc.Value = "" Then

    Set rngSrc = rngSrc.End(xlUp)

  End If

  If Not Intersect(rngSrc, Target) Is Nothing Then

    With rngSrc

      If strOldValue <> .Value Then

        .Insert Shift:=xlDown

        .Offset(-1).Value = strOldValue

        strOldValue = .Value

      End If

    End With

  End If

End Sub

Of course, you can only have one of these procedures in a worksheet module at one time. I have tried to design the routines so that they can be modified to accept additional ranges. If you run into additional problems please feel free to post another question on Yahoo!Answers.

Hope that helps...