Locking part of the Formula when 'Filling down'

[Okspaghetti]

I want to lock part of the reference so that when you copy/paste to the row below only one part of the formula changes.
Formula below for an example, I want to lock the section highlighted red

=SUMPRODUCT(--('Completed Permits'!</SPAN>C1:C223</SPAN>=A4),--('Completed Permits'!</SPAN>M1:M223</SPAN>="Yes"))

ATM when I copy,paste each reference in the formula changes as below.

=SUMPRODUCT(--('Sheet1'!C2:C224=A5),--('Sheet1'!M2:M224="Yes"))
=SUMPRODUCT(--('Sheet1'!C3:C225=A6),--('Sheet1'!M3:M225="Yes"))

I googled and looks like I need to use =INDIRECT, but do not know how to apply it to my formula.

Specifically the actual formula i need to apply it to is....
=IF(ISERROR(INDEX('Risk Matrix'!B21:G32,MATCH(D30,'Risk Matrix'!A21:A32,0),MATCH(C30,'Risk Matrix'!B19:F19,0))),"",INDEX('Risk Matrix'!B21:G32,MATCH(D30,'Risk Matrix'!A21:A32,0),MATCH(C30,'Risk Matrix'!B19:F19,0)))

But I used the example just so I can understand the process as it is shorter..


Any help is appreciated.
</SPAN>

 

[Mindpsyche]

I want to lock part of the reference so that when you copy/paste to the row below only one part of the formula changes.
Formula below for an example, I want to lock the section highlighted red

=SUMPRODUCT(--('Completed Permits'!C1:C223=A4),--('Completed Permits'!M1:M223="Yes"))

ATM when I copy,paste each reference in the formula changes as below.

=SUMPRODUCT(--('Sheet1'!C2:C224=A5),--('Sheet1'!M2:M224="Yes"))
=SUMPRODUCT(--('Sheet1'!C3:C225=A6),--('Sheet1'!M3:M225="Yes"))

I googled and looks like I need to use =INDIRECT, but do not know how to apply it to my formula.

Specifically the actual formula i need to apply it to is....
=IF(ISERROR(INDEX('Risk Matrix'!B21:G32,MATCH(D30,'Risk Matrix'!A21:A32,0),MATCH(C30,'Risk Matrix'!B19:F19,0))),"",INDEX('Risk Matrix'!B21:G32,MATCH(D30,'Risk Matrix'!A21:A32,0),MATCH(C30,'Risk Matrix'!B19:F19,0)))

But I used the example just so I can understand the process as it is shorter..


Any help is appreciated.

As you use a sumproduct formula, im sure you are aware of locking using $ signs? So try locking the desired range to be locked using $ signs.

 

[Sal Paradise]

Use $ signs before the reference.

$B1 will change to $B2 when you move down, and $B1 when you move across. (The column doesn't change)

B$1 will change to B$1 when you move down, and C$1 when you move across. (The row doesn't change)

$B$1 will stay $B$1 wherever you move it.

To apply to your formula, change C1:C223 to $C$1:$C$223 and M1:M223 to $M$1:$M$223

=SUMPRODUCT(--('Completed Permits'!$C$1:$C$223=A4),--('Completed Permits'!$M$1:$M$223="Yes"))

 

[Okspaghetti]

Ahhhhhhh, very simple solution. Thanks for your help.