+ Reply to Thread
Results 1 to 9 of 9

Circular Reference and/or Logic Issue

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Circular Reference and/or Logic Issue

    Tired and frustrated with this, so I'm asking for help as I can't figure out what's wrong. The file is attached.

    Here is the background info and the issue:

    In column C, I am entering a box label. So, something like D-5329. Sometimes that's the complete label. At other times, it's only a part of the label and I want appended to it a dash (-) and the number from the cell in Column A. So, D-5329-43. I want to check and see if there are duplicates, which I am doing in Column E, when I have new ones created. So, to distinguish between when the number in column A is to be appended or not, in Column D, when it's not to be appended, I am adding the following text after the words "Box Label:": "Itm#-N".

    Therefore, when there are duplicates, the words "Duplicate Label" is supposed to appear instead of the box label. However, something major is off. I keep getting inconsistent results and often times when I copy and paste the formula, I get the results of the last formula shown even though when I use Excel's Evaluate Formula, the correct result is shown as the 2nd to last step.

    Depending on the paste order or the order of the insertion of the text "Itm#-N" to Box Label, you will see Excel saying there is a circular reference when you go to Formulas > Formula Auditing > Error Checking > Circular References. It also tells me of this when I open the file for the first time.

    Also, in case you are wondering why I am using such things as
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    , instead of the cell number (i.e. C5), and also
    Please Login or Register  to view this content.
    it's because I want to be able to cut and insert cell rows without things breaking.

    Right now, as the file is, cells E6 is showing the results of cell E3 instead of the value of C6, which is D-9599.

    So, what's going on and what is the correct way to do the comparisons?

    Thanks.
    Attached Files Attached Files
    Last edited by roseuz; 11-29-2019 at 06:23 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Circular Reference and/or Logic Issue

    Your issue, simply, is that your duplicate check in Col E results in circular reference as every cell is a precedent of every other cell.

    You need to decide if every instance of a duplicate needs to be flagged, or only the 2nd/3rd instances etc

    If you need every duplicate flagged you should move the duplicate check to another column, entirely, this would remove the circular referencing
    If you need only 2nd, 3rd instances flagged then remove the 2nd COUNTIF such that each row looks only above itself

  3. #3
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Circular Reference and/or Logic Issue

    I only need the 2nd instance, so that as soon as I spot it when creating a new label, I can change it right away, but there is no guarantee that the duplicate might be located above. It could also be below. Can you elaborate more on the following: "your duplicate check in Col E results in circular reference as every cell is a precedent of every other cell."

    And for this example, are you able to tell if the check is being done if a different column?
    https://stackoverflow.com/a/17958143

    Thanks.

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Circular Reference and/or Logic Issue

    well, E3 has a dependency on E6 whilst, simultaneously, E6 has a dependency on E3 -- hence circular.

    based on your requirement I would think you need only ever look above - as every duplicate label will be flagged, at least once.

    re: the link, there's nothing that states in which cell the formula resides but if it were in B it would be circular.

    for completeness -- XL does have an iteration option when calculating however, in this instance the correct remedial action is to revisit the underlying logic being applied

  5. #5
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Circular Reference and/or Logic Issue

    Thanks for the response. I am inserting new rows between existing ones as I group things by category or location or original location. So, I may create a label of D-2395 and it may already exist above or below, so I do need to check both directions. I am also picking those numbers randomly.

    Now, instead of checking cell C and A in in E, can something like Sum Product work in which the comparison can be done in the following manner:

    In the case I am using the item number, check for the presence of C6, dash, and A6 in C6, dash, and A1, C6, dash, and A2, C6, dash, and A3, and so on?

    If yes, how exactly would that be written, and would I then need to press Ctrl + Shift + Enter for that type of formula, as I generally dislike those as I hate to always have to remember to press those keys to see the results.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Circular Reference and/or Logic Issue

    I think I am struggling to explain the concept -- consider following scenario:

    A1: =A2*10
    A2: =A1*10

    in this very simple setup we have a circular reference as A2 is dependent on A1 yet A1 is dependent on A2, and so the circle continues, forever.

    in terms of Iteration...

    SUMPRODUCTs/Arrays are often referred to as being "iterative" in nature given the way in which they process the precedent range however, in this specific context we're talking about Iteration as a general Calculation Option (refer Formulas option - google etc)

    In majority of cases Circular References can / should be fixed by adapting logic as outlined but, as always, there are legitimate use cases (e.g. Cash Flow), I'm not sure this is.

    At this point I am not sure I can really help you further as I can't really envisage how you would resolve this other than by one of those methods outlined in my prior post(s), namely:

    1. flag all instances of a duplicate in another column and remove the check from E altogether
    2. flag those instance where a duplicate appears in a prior row - if rows are inserted above, latterly, the dupes will still get flagged below

    I may be missing nuances of your problem but, ultimately, if you decide the circular referencing is obligatory you will need to enable iteration.

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Circular Reference and/or Logic Issue

    If you need to search both above and below, I suggest doing those checks in two separate columns, then check those columns to give the final result you want. That should avoid circular references, if I follow the logic correctly.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  8. #8
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Circular Reference and/or Logic Issue

    Thank you very much XLent and also Aardigspook. I may just create an extra column and then hide it, though I really dislike that type of setup, as it may be the easiest thing to do.

    The response of

    Please Login or Register  to view this content.
    posted to the question asked here https://www.reddit.com/r/excel/comme..._pair/cytlvcv/ I think would do exactly what I am trying to do, which is to compare two pairs of cells with another pair, though in my case rather than 4 columns, it's two cells with all other cells in the same two columns. So, taking out the dash, I want to compare C6 and A6 with C1 and A1, with C2 and A2, ... with C5 and A5, with C7 and A7, with C8 and A8, and so on.

    To pair it in a more illustrative manner (though I think you pros already know what I am saying).

    C6 and A6 with C1 and A1
    C6 and A6 with C2 and A2
    C6 and A6 with C3 and A3
    C6 and A6 with C4 and A4
    C6 and A6 with C5 and A5
    C6 and A6 with C7 and A7
    C6 and A6 with C8 and A8

    and so on.

  9. #9
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Circular Reference and/or Logic Issue

    Can't seem to edit my last post. Here is the direct link to the question I linked to: https://www.reddit.com/r/excel/comme..._another_pair/

+ 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 issue
    By Lugashz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2017, 01:20 PM
  2. Increase in Value, Circular Reference Issue
    By Mousiefuzz in forum Excel General
    Replies: 3
    Last Post: 10-27-2015, 07:05 PM
  3. Circular Reference Issue when Balancing Costs
    By yoman987 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-15-2014, 03:16 PM
  4. [SOLVED] circular reference issue
    By Netaji in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-02-2014, 09:47 AM
  5. Excel 2007 : Circular Reference Issue
    By henro8 in forum Excel General
    Replies: 1
    Last Post: 07-25-2011, 05:54 PM
  6. Circular reference issue. Looking for workaround.
    By krutec in forum Excel General
    Replies: 2
    Last Post: 12-11-2009, 03:20 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