Dave

You would need to capture a sheet event(the change of value in J21)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$J$21" And Target.Value < "" Then
ActiveSheet.Cells(Rows.Count, 12).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
Application.EnableEvents = True
End Sub

This code assumes L20 is not blank to start with.

Right-click on the sheet tab and "View Code".

Paste the above code into that module.

Enter something in L20 to start with. You can delete it after L21 is
populated first time the event fires.


Gord Dibben Excel MVP

On Tue, 1 Nov 2005 10:00:03 -0800, "Dave"
wrote:

Is there a Function or Formula that can store data from say cell $J$21 and
store the
result in L21 in the same worksheet and every time the data in $J$21 changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated