+ Reply to Thread
Results 1 to 10 of 10

Unable to Find the Circular Reference Problem

  1. #1
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    20

    Unable to Find the Circular Reference Problem

    Hi there

    I feeling like I am going crazy!

    I am getting a Circular Reference errors jumping all over a sheet I am in, but they are not obvious to me.

    So first it says H21 and I go into that Cell and then it moves to AP90.... is this normal?

    I feel like I am just getting random circular reference messages and not sure how to get out of this and now the Model is not calculating properly?

    Is this normal or has anyone got any suggestions on what to do and how to work out the issue?

    Many thanks

    Carina

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    901

    Re: Unable to Find the Circular Reference Problem

    If you could provide a sample file with the issue, it would go a long way to get help. Strip out any confidential information before uploading.
    What kind of formula are you using in those cells?
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    20

    Re: Unable to Find the Circular Reference Problem

    Thanks. Here is the attached sheet. The Circular References are jumping around all over the sheet.

    Could it be a BUG in excel? When I go to where it is, I cannot see an obvious CR?

    the formula I am using is a SUM or Addition. When one cell showed a problem with SUM I tried to just ADD the cells to see if that helped? No change but the issue is the way the CR seems to move around the whole sheet on me.
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,052

    Re: Unable to Find the Circular Reference Problem

    Unfortunately, the attachment is full of #REF errors... the data being calculated is on a sheet we don't hacve access to... so there's no circular error showing!! Can you have another go at uploading a standalone sheet?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    20

    Re: Unable to Find the Circular Reference Problem

    Hi Glenn

    Are you able to open the Excel without "updating" as its one of many sheets but it is too difficult to anonymise the whole workbook as the client name is all over the place inside inputs and this appears to be the sheet where the circular reference occurs?

    So I will upload it once again here, but try opening without updating and I hope it works.

    There is no REF showing in the standalone sheet here on my screen?

    Or am I missing something?

    Thanks for your help with this

    C
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    901

    Re: Unable to Find the Circular Reference Problem

    I get the same thing, even without updating the links.. get all #REF errors..

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

    Re: Unable to Find the Circular Reference Problem

    I am getting a Circular Reference errors jumping all over a sheet I am in, but they are not obvious to me. So first it says H21 and I go into that Cell and then it moves to AP90.... is this normal?
    I would say, yes, this is normal -- especially when the spreadsheet(s) are complicated enough that the circular reference/logic consists of several interconnected cells.

    Perhaps it is my older version, but I can open the latest file just fine without getting #REF errors. However, I also don't get a circular reference error, either. That suggests to me that the circular logic is flowing through the linked "Kelsey Farms Start Up..." spreadsheet as well. If that is correct, then this could be a fairly long and maybe complex string of calculations -- which usually means that debugging the circular reference error quickly becomes tedious and often frustrating.

    You say that Excel identified H21 (empty in this file) and AP90. Since AP90 is the only non-empty cell, I see that AP90 (a middle part of the Equity Account calculations) depends on the calculations to the left (eventually reaching some links to the other file). I also see that it is used by the further calculations to the right seeming to terminate in BF93.

    I don't have any good recommendations, other than to practice patience. There may be no tricks to solving this. Just starting in a cell that Excel identifies as part of the circular logic and follow the logic through that cells dependents and precedents until you can find where the circle closes.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    20

    Re: Unable to Find the Circular Reference Problem

    Thanks Mr Shorty! I solved it this morning. One cell was playing havoc! Persistence worked... I think I jumped to assuming it was an excel bug so asked on here in case it was before spending ages following the issue to the source.

  9. #9
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    20

    Re: Unable to Find the Circular Reference Problem

    PS can someone help me recall how to show it as SOLVED? I cannot seem to figure it out today!

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,052

    Re: Unable to Find the Circular Reference Problem

    It's in thread tools, just immediately above your first post...

+ 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. [SOLVED] Circular Reference problem
    By jontherev in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-08-2014, 08:19 PM
  2. Circular Reference Problem
    By neelpatel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-25-2012, 10:55 AM
  3. Circular reference problem
    By krishnamohan in forum Excel General
    Replies: 1
    Last Post: 08-12-2010, 09:33 PM
  4. Circular reference problem
    By yash in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-08-2009, 06:59 AM
  5. Circular Reference Problem - HELP
    By zero760 in forum Excel General
    Replies: 2
    Last Post: 12-01-2008, 09:01 PM
  6. Problem with Circular Reference
    By Paul M in forum Excel General
    Replies: 1
    Last Post: 02-14-2006, 09:45 PM
  7. Circular reference problem
    By R.Hocking in forum Excel General
    Replies: 4
    Last Post: 02-02-2006, 01:25 PM

Tags for this Thread

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