=LOOKUP(9.99999e+307,A:A)

should get you the last numerical entry in column A

FYI.. you have the quotes in the wrong place in that formula you posted.

s/b: =INDIRECT("'SHEET 1'!$D$" & COUNTA(D:D)) but that won't necessarily work, as it relies on you starting at D1. It counts how many entries are in column D and makes that the row reference to match. So if you have entries in D3 and D4 and expect the result to show what is in D4, it won't because it counts 2 entries in column D and so applies Sheet 1!D2 as the lookup cell and will probably return 0.