morsejon

MAX(IF(Sheet1!A$1:A1<>"",ROW(A$1:A1)))

Since Sheet!A$1 and ROW(A$1 are fixed, they won't change as you go down the row, but A1s change, increase by 1 as you go down.

Formula above returns the corresponding row number out of 1:1 (ROW(A$1:A1)) is equal to maximum row which contains any value within the range of A1 to A and current row on Sheet1.

Therefore, if you need to modify above portion, you need to match dynamic part of range which is A1 part to the first refernce row address.

In case of A7, the portion is like

MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7)))

then, in any cell on Sheet2

=IF(Sheet1!A7<>"",Sheet1!A7,INDEX(Sheet1!A$1:A7,MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7))),1))

Or, is this easier to understand?

=IF(Sheet1!A7<>"",Sheet1!A7,INDIRECT("Sheet1!A"&MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7)))))

Good luck

Jindon