+ Reply to Thread
Results 1 to 5 of 5

Circular Reference Greyed out

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    Guildford, England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Exclamation Circular Reference Greyed out

    Using Excel 2010, I have a large workbook, with lots of sheets, many pivots, and thousands of formulae including external references to other workbooks .
    Because of its size I have been turning autocalc on and off.
    I now have the message circular references in the status bar but have no idea where.
    If I go into formula auditing the circular reference bit is greyed out.
    Any ideas anyone?

    Thanks, Jesper


    I should add that I also get a message saying that Excel cannot calculate a formula. There are circ refs in an open workbook that cannot be listed. (nb only one open) Try editing the last formula or using Undo button. (I suspect it happened a while ago and there are no undo's and I have no idea where to look)
    Last edited by JesperAudi; 02-27-2013 at 10:44 AM. Reason: extra info

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,338

    Re: Circular Reference Greyed out

    Try doing an error check on each sheet. That should at least tell you which formula can't be calculated. Typically "Circular Reference" is gray if there are no circular references on the sheets. Maybe it's an issue with defined names. Hmmmm
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    09-12-2012
    Location
    Guildford, England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Circular Reference Greyed out

    Thanks ChemistB for reply.

    I'm not using names so that isn't the issue.
    The problem with error checking each sheet is that there are hundreds of errors eg dividing by zero etc
    I have started the idea of deleting each sheet without saving so I can see if it occurs on more than one sheet; I'll let you know how I get on.

    Thanks, Jesper
    I

  4. #4
    Registered User
    Join Date
    09-12-2012
    Location
    Guildford, England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Circular Reference Greyed out

    Well I guess that solved it; My last 4 sheets had quite ba lot of references to 'itself'.

    By deleting each sheet in turn I was able to narrow it down.
    In the end the circ ref message went off only when I had deleted the 4 last sheets ~ luckily thet were together

    Having found which sheets, I then had to reopen (without saving), go to those sheets and adopt the same approach to columns

    The problem affected several thousand rows of 4 columns, so eventually I was able to fix it.

    I will mark as solved in case anyone searches in the future, but I still don't understand why the formula auditing box was greyed out, and even if it was, why couldn't clicking in status bar at least take me to one example, rather than just saying too many to list. One would have helped.
    #

    Anyway chemistB, thanks for your he;lp.

    Regards, Jesper

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,338

    Re: Circular Reference Greyed out

    Glad you figured it out. You may be right, it might be grayed out because there are too many to list.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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