auto fill an indirect formula

Hello, I've looked all over the net trying to find a solution, but here I am. Your help is appreciated.

I am in need a formula (for "'Sheet1'") to find the smallest number in a range of cells from ("'Sheet2'"). But, I also need that formula reference to the cell range on "'Sheet2'" to be absolute so that I can insert colums in "'Sheet2'"without the formula automatically updating which column it references.

The problem is that I need to be able to auto fill the formula on "'Sheet1'".

I've been researching, guessing, and testing for 4 hours now and here
Below is the closest thing to my desired formula that I've gotten to work.

=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J4:$N4)),1)

But, when I auto fill, it comes out like this:

=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J4:$N4)),1)
=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J5:$N5)),1)
=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J6:$N6)),1).....and so on

I need it to come out in a way that the column reference is absolute, but the row changes (preferably based on the row that the formula is in)

Please, help me.

EXAMPLES of desired final ranges:


=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J4:$N4)),1)
=SMALL(INDIRECT("'Sheet2'!$J5:$N5"&ROWS('Sheet2'!$J5:$N5)),1)
=SMALL(INDIRECT("'Sheet2'!$J6:$N6"&ROWS('Sheet2'!$J6:$N6)),1).....and so on.

Is that what you were asking?

Yes,

J4:N4
J5:N5
J6:N6...etc

Yes! Thank you so much. That is exactly what I needed.

I've not used the INDEX function before. I'm curious, how do you determine to use "$65536"?