In G2 copied down, try:
=LOOKUP(2,1/(--NOT(ISBLANK(INDIRECT("'" & $A2 & "'!F1:F9999")))),INDIRECT("'" & $A2 & "'!F1:F9999"))
this uses NOT(ISBLANK( rather than >0 in case the last value is negative.
It also limits how many rows are taken into account using F1:F9999 rather than F:F to save on recalculation time; adjust this 9999 to a coverall value that will never be exceeded on those other sheets.