Here is a formula that may be more or less what you need

=INDIRECT("A"&COUNTA(A1:A100))

This formula will cause the cell that contains it to display the
contents of the last cell in column A that isnt blank. For the
example, I only had it count from A1 to A100, but you may change that
to whatever number you wish. This will only work for you if there are
NO BLANK CELLS within the range being counted before the last non blank
cell.

Your actual formula may look more like this:
=INDIRECT("A"&COUNTA(Sheet3!A1:A10))
due to the fact that you are traversing different worksheets. If you
have any questions, gimme a shout.