Circular Reference when formulas reference end of row formula!

1. 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  Register To Reply