Locked formula changes reference when rows added to reference sheet in same workbook

I have a formula which works as I need it to work -- at least the first time I use it. That formula is listed as "Original Formula", below. My problem with the formula is that it refers to a separate sheet ("Locked Loans"), and the Locked Loans sheet has a macro which inserts two new rows between categories of loans, based on the data that is uploaded. So every time the macro runs, my formula below adds two rows to the sections I highlighted in red/bold. For example, I ran the macro once, and it changed my Original Formula (below) to the formula called "Adjusted Formula". I would like for the Locked Loans sheet reference to always evaluate rows #13-1,000; however, even with those cells locked (with the "$" sign) they adjust when rows are added to the Locked Loans sheet. Any creative thoughts? Thanks very much, in advance.

Original Formula

Please Login or Register  to view this content.

Adjusted Formula

Please Login or Register  to view this content.

You can use INDIRECT to ensure that the range does not vary, as the references within the INDIRECT function are actually text values so do not change when rows are inserted. Your original formula would become:

=IF($C62=0,"",IF($C62="","",((SUMPRODUCT((1-ISNUMBER(MATCH(INDIRECT("'Locked Loans'!$AA$13:$AA$1000"),$AC$60:$AJ$60,0)))*(INDIRECT("'Locked Loans'!$E$13:$E$1000")=$A62)*INDIRECT("'Locked Loans'!$C$13:$C$1000")))/$C62)))

Hope this helps.

Pete

Pete_UK, martindwilson and TMShucks -- those options work! Thank you very much!!! Have a nice day.