+ Reply to Thread
Results 1 to 13 of 13

Circular Reference Error. One for the pro's :-) Sheet attached.

  1. #1
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    207

    Circular Reference Error. One for the pro's :-) Sheet attached.

    I made a spreadsheet for my wife's floral business to help her calculate floral arrangement pricing quotes.

    I did so with a bunch of formula's and conditional formatting to make an easy to use sheet for her...but what I'm left with is a circular reference warning error when she try's to build her second arrangement and I can't seem to figure it out.

    I'm hoping someone can take a look at the sheet and try and solve my circular reference error.

    I will warn you this is a pretty complex sheet, for those up to the challenge ;-)

    I tried to attach the file...I'm not sure its going to show. Let me know if you can't see it.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    207

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    On the Items tab and arrangements tab you'll find the circular reference error.
    Last edited by unclejemima; 11-22-2017 at 01:51 AM.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    There is nothing particularly complex about this spreadsheet.

    A circular reference comes about where you have calculations that are dependent upon each other and cannot, therefore, be resolved. For example, if I have a calculation in A1 that is dependent on a calculation in A2 that is in turn dependent on the calculation in A1, Excel is never going to be able to resolve it. This is what you have on both sheets in question, so you are going to need to restructure the way those sheets do their calculations in order to break the circle.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    Trying to ID a circ ref error and be troublesome, excel TRIES to show you where it is, but often, the actual error lies deeper.

    It seems to me that the problem is coming from Items K2 and N2, both of which use this...
    SUMIF('Arrangement Builder'!C:C,B2,'Arrangement Builder'!I:I)
    whic then is used to build the total in S

    However, that total is being pulled into ARRANGMENT G4 - which is being used back in Items

    (on a side note, it's always better to not give is a protected file, we need to be able to test and play (I took off the protection easy enough, just saying though)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    207

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    Quote Originally Posted by AliGW View Post
    There is nothing particularly complex about this spreadsheet.
    Spoken like a Pro :-) I mean that as a compliment as I find it complex lol. But when you know what your doing, I suppose its not!

    Quote Originally Posted by FDibbins View Post
    (on a side note, it's always better to not give is a protected file, we need to be able to test and play (I took off the protection easy enough, just saying though)
    My apologies. I thought the sheet I gave was unprotected. There is no password on it if that counts, so you can unprotect it by simply clicking "unprotect sheet" if anyone wants to try and correct it.

    I'm going to give it a-go again based on the advice from you guys. Thanks!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    Let us know how you get on.

  7. #7
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    207

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    Ok. I gave it a go again.

    Simply deleting the formula in 'Arrangement Builder' H24 fixes this circular reference error...but I need that calc. in order to total everything. Its root cause is as suggested, is probably from 'Items' K2 and N2

    Problem is 'Items' K2 and N2 is the only creative way i could calculate the totals of each flower in the 'Arrangement Builder' Sheet(example, Lg Sunflower) in a vlookup type solution, but gathering sum from more than one vlookup (where vlookup will return only the first value it lands on in my understanding)

    So if anyone can figure a creative solution out I would be very grateful :-) I'm stuck :-(

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    Could you attach the latest version of your workbook? I’ll have a look in the morning if nobody else has jumped in before then.

  9. #9
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    207

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    Thanks AliGW.

    Attached is the unprotected sheet. Entering the value "Lg Sunflower" in 'Arrangement Builder' C24 will start the whole circular reference thing.

    The circular reference error is also visible on the 'items' tab. They are both related.

    If you can figure it out I'll be extremely grateful!!!

    Thank you!!!!!!!!!!!!!
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    What strategies do you use when dealing with circular references? I like to manually enter the value shown in the cell(s) indicated by Excel until I better understand the circular reference, so I can think through the circular logic. Here's what I tried:

    1) On "items", I entered 8 into N2, which seemed to clear that circular reference, but showed a new circular reference in K2. Entered 8 in that cell and all circular references are gone. Both of these formulas contain the same conditional sum, summing column I of "Arrangement Builder". Undo to restore formulas.
    2) Look at column I of "Arrangement Builder". The Bride Sharon section of this sheet is multiplying column D with G1, and no circular references are indicated. The circular reference is limited to the Bridesmaids section, where it is multiplying column D by H21. Is this correct, or should the pattern be the same here as for the previous section? I note that by entering 0 into this cell, the circular reference is broken. If I enter 3.5 into this cell, the values change, but the circular reference is still broken.

    The circular reference/logic seems to hinge on Arrangement Builder!I24 (until additional flowers are added to additional rows). What is supposed to be happening in column I below I24? How would you calculate these cells outside of Excel (using pencil and paper and hand calculator for example)? Not knowing or understanding the intended logic, I cannot recommend a solution.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  11. #11
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    207

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    Son of a gun!!!!! You've hit the nail on the head. I spent 20 minutes writing a reply to your first point...then I analyzed your second point....sure as poo I messed up. In the bridesmaids section, the formula was supposed to be D by G21, not H21! I spent a few late nights on this and this slipped by me. I even checked this before posting my issue but never noticed.

    I'm fairly sure this has 100% solved the problem!!!!!!!
    Last edited by AliGW; 11-25-2017 at 03:29 AM. Reason: Unnecessary quotation removed.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  13. #13
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    207

    Re: Circular Reference Error. One for the pro's :-) Sheet attached.

    Quote Originally Posted by AliGW View Post
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Done and done!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Circular reference error
    By Furin Hoang in forum Excel General
    Replies: 10
    Last Post: 06-06-2017, 11:21 AM
  2. [SOLVED] Circular reference error
    By Nitro2481 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-10-2015, 08:04 AM
  3. [SOLVED] Circular Reference Error
    By jackleesteere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2014, 10:02 AM
  4. [SOLVED] Circular reference error beyond sheet data
    By vonborge in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-04-2013, 03:37 PM
  5. Circular reference error
    By Cjax in forum Excel General
    Replies: 5
    Last Post: 03-24-2011, 10:10 PM
  6. Circular Reference Error
    By nevi in forum Excel General
    Replies: 3
    Last Post: 07-05-2010, 04:51 PM
  7. Circular reference error
    By Rick in forum Excel General
    Replies: 1
    Last Post: 10-05-2005, 04:05 PM

Bookmarks

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