+ Reply to Thread
Results 1 to 1 of 1

Circular Reference when formulas reference end of row formula!

  1. #1
    Forum Contributor
    Join Date
    MS-Off Ver
    Office 2013

    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.


    or by way of an alternative:


    Originally, I used this formula:


    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:


    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:


    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
    Attached Files Attached Files
    Last edited by VBA Noob; 03-29-2009 at 10:21 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1