You need event code to do what you want. Try this... right click the tab at
the bottom of the worksheet where you want this functionality, select "View
Code" from the popup menu that appears and copy/paste the following into the
code window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Address(0, 0) = "C3" Then
LastRow = Range("C200").End(xlUp).Row
If LastRow = 5 Then Exit Sub
If LastRow = 3 Then LastRow = 5
If LastRow < 200 Then Cells(LastRow + 1, "C").Value = Range("C3")
End If
End Sub

That's it. Now go back to the worksheet and type something into C3... it
will be placed into the first empty cell in the range C5:C200.

--
Rick (MVP - Excel)


I am tracking a bank account balance and would like that whenever a new
value
is entered in cell c3, it updates the next consecutive empty cell in range
c5:c200. Please advise how I can write the formula, function or macro.

Thanks Sony