+ Reply to Thread
Results 1 to 3 of 3

How to avoid circular reference errors with intentional circular references

  1. #1
    Registered User
    Join Date
    02-23-2014
    Location
    ki
    MS-Off Ver
    k
    Posts
    1

    How to avoid circular reference errors with intentional circular references

    Hi

    I'm trying to create a basic Excel document intended for use as an navigation log.

    I'm having a problem with circular references.

    Here is what I'm trying to do:
    True track: A1=B1-C1
    Mag. track: B1=A1+C1
    Mag. variation: C1

    The idea being when you open the document you enter the true track or magnetic track depending on the information derived from the chart. The document then calculates either mag. or true track for you depending on where you entered the information. I then have 25 or so rows of this.

    The problem I'm having is that once excel "sees" any circular references in the sheet it brakes down and some other functions stop working. Since you usually don't use all the rows available the document will always contain these circular references.

    I know I can disable the circular reference warning and that fixes the issue but as I'm intending to share the log I'd rather find another solution.

    I tried IFERROR and I couldn't get it to work.

    Any suggestions?

    Is there no function similar to IFERROR so that if a function contains a circular reference it will return some fixed value?

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to avoid circular reference errors with intentional circular references

    It sounds like you are trying to enter data into cells that have formulae. You can have one or the other but not both
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to avoid circular reference errors with intentional circular references

    Hi,

    The only way you can avoid this is by using a Sheet Change macro that detects the cell in which you are entering a number and which then populates the appropriate cells with the relevant formulae.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the 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] How to avoid circular reference
    By CHoedeman in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 10:07 AM
  2. Technique to avoid circular reference
    By smjpl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2012, 10:34 AM
  3. Trying to Avoid a Circular Reference
    By dpmarkey in forum Excel General
    Replies: 1
    Last Post: 05-27-2010, 02:07 PM
  4. Intentional Circular
    By warburger in forum Excel General
    Replies: 4
    Last Post: 10-26-2006, 08:00 PM
  5. Need to avoid a circular reference...
    By Denise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 04:06 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