Drag Down Formula whilst retaining some values but change another

Question

I'm looking for assistance with a problem i'm having when using Excel. The best way to explain the problem i'm encountering is that i'm using the auto-fill drag down to copy and paste essentially a formula.. I'm currently using this function on sheet 1:

 =sum('Sheet2'!E2*Sheet3!B3+Sheet3!B2)

Now, when dragging down the formula it changes which is expected, but not my intended results. The formula changes to the following:

=sum('Sheet2'!E3*Sheet3!B4+Sheet3!B3)
=sum('Sheet2'!E4*Sheet3!B5+Sheet3!B4)

My intended result is to have one reference cell change, but keep the last two in the formula the same reference as such:

 =sum('Sheet2'!E2*Sheet3!B3+Sheet3!B2)
 =sum('Sheet2'!E3*Sheet3!B3+Sheet3!B2)
 =sum('Sheet2'!E4*Sheet3!B3+Sheet3!B2)
 =sum('Sheet2'!E5*Sheet3!B3+Sheet3!B2)

With the intended first row populated with the formulas:

=sum('Sheet2'!E2*Sheet3!B3+Sheet3!B2)
=sum('Sheet2'!F2*Sheet3!C3+Sheet3!C2)   
=sum('Sheet2'!G2*Sheet3!D3+Sheet3!D2)   
=sum('Sheet2'!H2*Sheet3!E3+Sheet3!E2)   
=sum('Sheet2'!I2*Sheet3!F3+Sheet3!F2)   
=sum('Sheet2'!J2*Sheet3!G3+Sheet3!G2)   
=sum('Sheet2'!K2*Sheet3!H3+Sheet3!H2)   
=sum('Sheet2'!L2*Sheet3!I3+Sheet3!I2)

as the drag down formula changes from row to row, the last two references need to remain the same on sheet 3, but sheet 2 change as the row increases, so

'Sheet2'!E3
'Sheet2'!E4
'Sheet2'!E5
'Sheet2'!E6

Answer 1

You need to add the $ symbol before the portions that you don't want to change, such as:

=sum('Sheet2'!E2*Sheet3!B$3+Sheet3!B$2)