How to apply formula in 1000 rows but with fixed cell positions that are all different

[systems32]

I need a sheet that has 1000 rows and that should have in each cell different fixed position
I have 10 columns and 1000 rows and I need the formula to be pasted dynamically so it changes the row and cell number but after that I need all of the cells to have fixed value that does not change if I add a row or a column.

If I drag fixed value:
='RESPONSES-PUNJENJE'!$D$2
='RESPONSES-PUNJENJE'!$D$2
='RESPONSES-PUNJENJE'!$D$2

If I drag without $
='RESPONSES-PUNJENJE'!D2
='RESPONSES-PUNJENJE'!D3
='RESPONSES-PUNJENJE'!D4

I need this. And I guess there is some solution not to do this manually.

='RESPONSES-PUNJENJE'!$D$2
='RESPONSES-PUNJENJE'!$D43
='RESPONSES-PUNJENJE'!$D$4
='RESPONSES-PUNJENJE'!$D$5
='RESPONSES-PUNJENJE'!$D$6
='RESPONSES-PUNJENJE'!$D$7
='RESPONSES-PUNJENJE'!$D$8
='RESPONSES-PUNJENJE'!$D$9

[BMV]

drag without $
='RESPONSES-PUNJENJE'!$D2
='RESPONSES-PUNJENJE'!$D3
='RESPONSES-PUNJENJE'!$D4
then
Ctrl+H
!$D to !$D$ replace all.

[Glenn Kennedy]

Open the attached file. Look at the yellow cells. they are copied across from sheet 2.

Select the yellow cells.

CTRL-H Find: 2!D replace: 2!$D$

OK.

Now enter rows columns on either hseet.

[kvsrinivasamurthy]

In B2
=INDEX('RESPONSES-PUNJENJE'!$D$2:$D$100,ROWS($B$2:$B2))

Or in D2

=INDIRECT("'RESPONSES-PUNJENJE'!$D$"&ROW())

then drag down for 1000 rows.

Indirect is a passive formula it may slow down if data is very large.