#### Okspaghetti

##### New Member

- Joined
- Oct 26, 2011

- Messages
- 12

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>