In the diagram below, Column A to D represents DATA like (key in)

A: Date
B: Document Number
C: Sales Person
D: Amount

Column E to G represents FORMULA like
E: Vlookup / Index Match
F: SumIF
G: + - x /

Sheet 1
------------Data-------------+---Formula-----Column
-----A-----B-----C-----D-----E-----F-----G
1---
2---
3---
4---
5---
6---
7---
8---
9---
Row

Current Method:
Lets say the FORMULA row is only up to E3 after the latest access (3 rows of
data). And after some data entry the DATA line it reaches Row 9.

What I will do is copy / drag E3:G3 downwards to row 9 and the formula is
pegged for each line. (Manual)

I use lots of Pivot Table. The structure of the source data is as per
diagram. One row to contain all information.

When your codes contain R1C1, that is beyond my ability.

Your codes do automate, however its a little tough! I guess I am using
surface functions to steer IN the VBA (that deserves a kick in the ***) Thats
why I have lots of hard code and long formulas!

For the Len thing: Copy and Paste !!! (extracted from the script below)

The VBA script below is for fixed cell but it does not automatically peg a
data line. (Acquired earlier)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count 1 Then Exit Sub
If Len(Target.Value) 0 Then Exit Sub

Application.EnableEvents = False

If Target.Address = "$E$6" Then Target.Formula= "=YEAR(TODAY())"

If Target.Address = "$F$6" Then Target.Formula=
"=SUMIF('31'!S:S,CONCATENATE(G6,""-"",L6),'31'!H:H)"

If Target.Address = "$G$6" Then Target.Formula=
"=IF(ISERROR(LEFT(INDEX('31'!L:L,MATCH(CONCATENATE (G6,""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)))-1)),"""",LEFT(INDEX('31'!L:L,MATCH(CONCATENATE(G6, ""-"",L6),'31'!S:S,0)),SEARCH(""-"",INDEX('31'!L:L,MATCH(CONCATENATE(G6,""-"",L6),'31'!S:S,0)))-1))

Trial & error!!!!

Hey Bob, thanks ! Appreciate your effort and time. I will use the codes you
have given me.