Lookup final row of indirect tabs

[Jak7217]

If I have 3 separate tabs of data, call the tab names Tom, Julie, and Ron. Column B of each of those 3 tabs will have a final number, aka the last row of data populated. I want to have a summary tab that will name all 3 of those people off and provide that final number.

I've done lookup formulas before to grab the final row of data and I've used indirect to dynamically reference a tab, but I have never combined them. I can't get the formula below to work - is this the best way or is there a better way?

Formula below: Consider the reference to A4 to be the name "Tom", trying to reference an excel tab named "Tom"

=LOOKUP(2,1/(INDIRECT(ADDRESS(1,2,,,A4),(INDIRECT(ADDRESS(1,2,,,A4))))))

 

[Peter_SSs]

If the last value is a number, does this do what you want?

=LOOKUP(9E+307,INDIRECT("'"&A4&"'!B$1:B$1000000"))