I tried
=INDIRECT('SHEET 1'!"$D$ & COUNTA(D:D)) - there's a mistake in the code.
I even tried it by adding $D2$, still the same.