+ Reply to Thread
Results 1 to 13 of 13

circular reference issue

  1. #1
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    53

    circular reference issue

    hi,

    I have the following issue with circular reference.I have enabled circular reference in formula settings with max iteration=1

    but still (if) condition produces wrong result.i have given the formula in "c2". C2 VALUE IS 98 and D2 VALUE IS 63.65.The condition is actually not met.But still formula produces as "ACHIEVED".

    =IF(C2="ACHIEVED","ACHIEVED",IF(C2<D2,"ACHIEVED","")

    Can anybody help on this issue?

    regards,

    Netaji

  2. #2
    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: circular reference issue

    Are you saying that you have this formula in C2?
    And if so why? Why not put it somewhere else so that it isn't a circular reference?
    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.

  3. #3
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: circular reference issue

    Thanks for your Correction.I have the formula in "B2".I made a mistake by saying formula in C2.I just want to stop the condition from running again if the condition is met.Data in C2 is not static.It keeps changing every second.That's why i need this way.

    =IF(B2="ACHIEVED","ACHIEVED",IF(C2<D2,"ACHIEVED","")

    Thanks.
    Last edited by Netaji; 03-01-2014 at 02:02 PM.

  4. #4
    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: circular reference issue

    Hi,

    In that case you can only do this with a macro sheet change event that looks at the C2 value every time C2 changes, i.e. every second and once the condition is met then the B2 value is converted to a Value.

    What's causing C2 to change. Is this a connection to an external data source/

  5. #5
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: circular reference issue

    Thanks for your response.Yes it is external data source,Linked with stock trading terminal.C2 is the value of current market price of a stock.So it changes quite often.

    Thank you

  6. #6
    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: circular reference issue

    Hi,

    In tha case maybe

    Please Login or Register  to view this content.
    End Sub

  7. #7
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: circular reference issue

    Thanks Richard.It really worked great.How to modify the same code if it is applied between a range ("C2:C18")<("D2:D18")

    Regards,

    Netaji

  8. #8
    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: circular reference issue

    Hi,

    You'll need to define precisely what you mean. Do you mean that EVERY cell in C2:C18 must be less than it's equivalent in D2:D18, or perhaps ANY cell.

    Either way I'd set up a helper cell with a function that evaluates the condition you're testing for make it return say either True or False. Then the macro can check this helper cell and the line of code would be:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: circular reference issue

    Richard,
    Richard,

    Just assume how we would drag down the formula from B2 to B18 had we entered formula manually in B2.I just want the same thing in the macro you had sent earlier.Great thanks for your follow up.

    Regards

    Netaji

  10. #10
    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: circular reference issue

    Sorry this is not clear to me.

    The macro has a test in it. The result of the test determines what appears in cell B2. Or that's what your post #3 says.
    Hence you need to define the rules of the test as I said.

    Are you perhaps suggesting that rather than the single B2 cell there are several cells that need a result?

  11. #11
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: circular reference issue

    Richard,

    I have tried to describe in the attached image what i am actually looking for.

    Thanks
    Attached Images Attached Images

  12. #12
    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: circular reference issue

    Sorry, can't open that file for whatever reason.

    If perhaps you are wanting to test several cells then perhaps

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: circular reference issue

    Richard,

    You got it.I had exactly same thing in the attached image.Thanks for putting effort.

    Regards,

    Netaji

+ 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. Replies: 2
    Last Post: 02-23-2014, 06:06 PM
  2. [SOLVED] Circular reference issue and External data
    By alarm_guy1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2012, 08:16 AM
  3. Circular reference causing formula issue
    By spacemonkey82 in forum Excel General
    Replies: 3
    Last Post: 09-04-2011, 04:49 AM
  4. Excel 2007 : Circular Reference Issue
    By henro8 in forum Excel General
    Replies: 1
    Last Post: 07-25-2011, 05:54 PM
  5. 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