find last row of data and dynamically change formula in that row

You could try something like this in cell H1:

=IF(ROWS($1:1)>COUNTA(A:A),"",IF(ROWS($1:1)=COUNTA(A:A),INDEX(F:F,COUNTA(A:A)-1),MAX(A$1:A1)))

then copy down. If you have 1150 rows of data (as measured by COUNTA(A:A), assuming there is no other data in that column below the main data), then it should return the value from F1149 in cell H1150 and in cells above that it will return the MAX from A1 to the current cell. Cells beyond H1150 should be blank. You could put =COUNTA(A:A) in a helper cell somewhere, and refer to that cell (3 times) in the formula.

Hope this helps.

Pete

HI Pete, thanks! I'll give it a go.