Circular Reference when formulas reference end of row formula!

Hi all

Spent some time on this but although I have found a viable work around to the problem, I would like to ascertain what is causing the circular reference and if there is a better way of resolving it.

If you refer to the ‘Collections’ worksheet, Column B uses a formula to ascertain the last used cell but one in the relevant row.

=INDEX(K7:CP7,MATCH(9.99999999999999E+307,K7:CP7)-1)

or by way of an alternative:

=INDEX(Table_Collect,ROW(),MATCH("*",$3:$3,-1)-11)

Originally, I used this formula:

=SUM(OFFSET(L12,-COUNTIF($B$5:$B11,$B11),,):OFFSET(L12,-1,0))

to create totals in Columns L to AB and whilst this did not conflict with the formula in Column B, it does not handle inserting and deleting rows correctly.

I then opted for the following formula:

=SUMIF($B$5:$B$96,OFFSET($B12,-1,,),L$5:L$95)

to create the totals but this causes a circular reference when used across the row with either of the above ‘last used cell formulas’ in Column B.

To resolve this, I amended the range for the SUMIF formula to:

=SUMIF($A$5:$A$96,OFFSET($A12,-1,,),L$5:L$95)

Which appears to be a workable solution. However, I would prefer to reference the ID number in Column B as this is generally the basic connection with other formulas and worksheets.

I would appreciate any comments as to how the circular reference can be avoided without iteration or whether there is a better more viable alternative to achieve my objective.

TIA …spellbound