[1] If one is interested in the last numeric value in column A...
=LOOKUP(9.99999999999999E+307,Sheet1!A:A)
[2] If one is interested in the last text value (which can be a formula blank) in column A...
=LOOKUP(REPT("z",255),Sheet1!A:A)
Note that the lexical ordering is ASCII-based.
[3] If one is interested in any value (e.g., number, date, time, truth value (TRUE/FALSE), and error value...
=IF(ISBLANK(Sheet1!A65536),LOOKUP(2,1/(1-ISBLANK(Sheet1!A1:A65535)),Sheet1!A1:A65535),Sheet1!A65536)
This is a pretty expensive formula, so it should not be invoked in lieu if either [1] or [2].
What to invoke if column A consists of numbers (recall that dates and are also sumbers) and text like ""?
Obviously, [3] can be invoked. An alternative would be a set of fast formulas...
B1:
=MATCH(9.99999999999999E+307,Sheet1!A:A)
B2:
=MATCH(REPT("z",255),Sheet1!A:A)
B3:
=INDEX(Sheet1!A:A,CHOOSE(COUNT(B1:B2)+1,"",SUMIF(B1:B2,"<>#N/A"),MAX(B1:B2)))