|There are 2 ways to automate the process:|
1 - Obviously, you could drag the formula down beyond the existing data so that the formula is "waiting" for Column A to be populated. There's an option to hide zero values so the cells would be blank until Column A was populated.
Since the show/don't show Zero Values option is for the whole sheet, that might be an issue if you need to see some zero values but don't want to see them in Column F.
In that case, you could an IF function in Column F. Read up on the IF function in Excel help to see how it works.
=IF(A1 <> "", A1, "")
2 - If you don't want a bunch of formulas filling Column F waiting for input in Column A, you could use a WorksheetChange macro:
Private Sub Worksheet_Change(ByVal Target As Range)
'Was change made to Column A?
If Target.Column = 1 Then
'If yes, then populate Column F with the value from Column A
Range("F" & Target.Row) = Target
Whenever a change is made to Column A, the corresponding cell in Column F will show the same value.
Posting Tip: Before posting Data or VBA Code, read this How-To.