+ Reply to Thread
Results 1 to 11 of 11

Circular reference error

  1. #1
    Registered User
    Join Date
    06-04-2017
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    5

    Question Circular reference error

    Dear all,

    I am getting stuck in circular reference error in excel, pls see my attachment for your sample ( the 1st sheet is original, the second one is the result after I inputting manually )

    Take case A1000001 ( code) as example, yellow row "Base" at the bottom of sub-table is my formular which I based on to input numbers in row "Plan ordered"
    For instance, initial value of cell AI14 is 7500 and AJ14 is 37500, when I inputted 7500 in cell AI9, value of AI14 changed into 0 and AJ14 turned into 30000, then I will pick up 30000 to continue inputting that figure in cell AJ9, then keep repeating same manipulation until you meet the yellow cell of which value is zero

    My desired result is setting value of AJ9 equal to value of AJ14 after I inputted a number in AI9, but it occurred circular reference error, I have already enabled iterative calculation but all cells in row "Plan orders" displayed 0. My excel knowledge is still limited and I am a non-VBC coder. So can anyone suggest solutions to help me out?
    Any help would highly be appreciated !
    Attached Files Attached Files

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

    Re: Circular reference error

    I am not sure what you are trying to do. When you want to use circular references and circular logic, you must have a much better understanding of the logic you are trying to employ before you will be successful at programming that logic into the spreadsheet. Here's what I see, based on what I can understand.

    By putting 7500 (or anything else larger than 90.7 (related to the value in AJ13) into AI9, then AI14 will be 0. Making this a "circular reference" (AI9=AI14) will cause AI9 and AI14 to oscillate between 0 and 7500. Copying that across so that row 9=row 14 all the way across does the same thing, it sets up an oscillation between the current values and 0. If you go into Excel options and set max iterations to 1, then you can press F9 and follow this oscillation.

    I cannot make any suggestions, because I do not understand the logic you are trying to implement. All I can suggest at this point is to study the circular logic you are trying to create until you understand it better (I even wonder if it is supposed to be circular). We might be able to make better suggestions if you can explain the logic to us better.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-04-2017
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    5

    Re: Circular reference error

    Quote Originally Posted by MrShorty View Post
    I am not sure what you are trying to do. When you want to use circular references and circular logic, you must have a much better understanding of the logic you are trying to employ before you will be successful at programming that logic into the spreadsheet. Here's what I see, based on what I can understand.

    By putting 7500 (or anything else larger than 90.7 (related to the value in AJ13) into AI9, then AI14 will be 0. Making this a "circular reference" (AI9=AI14) will cause AI9 and AI14 to oscillate between 0 and 7500. Copying that across so that row 9=row 14 all the way across does the same thing, it sets up an oscillation between the current values and 0. If you go into Excel options and set max iterations to 1, then you can press F9 and follow this oscillation.

    I cannot make any suggestions, because I do not understand the logic you are trying to implement. All I can suggest at this point is to study the circular logic you are trying to create until you understand it better (I even wonder if it is supposed to be circular). We might be able to make better suggestions if you can explain the logic to us better.
    The circular logic here is value of cell AI9 is included in formular of cell AI13, then value of AI13 is contained in formular of cell AJ13, the value of cell AJ13 determines result of cell AJ14 which I need to capture to input in cell AJ9. Hence, value of latter cell depends on inputted value of former cell, that pops up circular reference warning when I tried to set up AI9=AI14, AJ9=AJ14,AK9=AK14, etc. You can see my desired result in sheet 2 for ref

    I followed your guidance and result is the same as I simply copy all cells in yellow row and paste into row "plan order", probably due to my ambiguos explaination ( sorry I'm a non-native English speaker). I used to input one by one in row "plan order" manually because I can't figure out a better solution.
    It comes up a thousand of item codes, so it takes me lots of time to struggle to input each cell manually.
    Hopefully you get my point to find out an automatic solution. Thanks in advance !

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

    Re: Circular reference error

    First thing I notice looking at the "result" tab -- row 9 is not equal to row 14 when the calculation is done. A function like =-AJ14 in AJ9 will only work if the final result ends with AJ14=AJ9 (and the rest of row 14=row9).

    It appears to me that this is more of a two stage calculation rather than a true circular calculation.
    Stage1 -- perform calculation assuming row 9 is zero
    Stage2 -- perform calculation again with the values in row 14 in row 9.

    I have not tried to reverse engineer or think through the details of your calculation -- I assume you can do that. I would probably add a few rows below row 14, then use those rows to perform the stage 2 calculation.

    Can I assume that you can figure out the stage 2 calculation?

  5. #5
    Registered User
    Join Date
    06-04-2017
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    5

    Re: Circular reference error

    Quote Originally Posted by MrShorty View Post
    First thing I notice looking at the "result" tab -- row 9 is not equal to row 14 when the calculation is done. A function like =-AJ14 in AJ9 will only work if the final result ends with AJ14=AJ9 (and the rest of row 14=row9).

    It appears to me that this is more of a two stage calculation rather than a true circular calculation.
    Stage1 -- perform calculation assuming row 9 is zero
    Stage2 -- perform calculation again with the values in row 14 in row 9.

    I have not tried to reverse engineer or think through the details of your calculation -- I assume you can do that. I would probably add a few rows below row 14, then use those rows to perform the stage 2 calculation.

    Can I assume that you can figure out the stage 2 calculation?
    Dear Mr Shorty,
    Sorry if I misunderstood your assumption. In my opinion, Stage 1 as you assumed can be omitted, can't it? Because in my file, they are already set up 0 ( pls focus your implementation only in Sheet1 )
    You can freely add more rows or VBA codes to facilitate your calculations , as long as you figure out an automatic solution for this matter.
    I am genuinely looking forward to your prompt response that helps me a lot

  6. #6
    Forum Contributor
    Join Date
    06-29-2016
    Location
    Bristol, England
    MS-Off Ver
    2007
    Posts
    355

    Re: Circular reference error

    make sure you have turned on 'iterations' in excel options on the office button

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

    Re: Circular reference error

    I really don't understand the overall calculation. I was hoping/expecting that it would be easier for you to edit the spreadsheet than for me, since you are in a better position to understand the calculation.
    In my opinion, Stage 1 as you assumed can be omitted, can't it?
    This is where it is important to understand the algorithm you are trying to omit. Stage 1 seems necessary to me. However, you are the expert in the algorithm, so I would have to defer to your judgement in this.

    Assuming stage 1 is necessary, this seems to work, but, like I said, I don't understand the overall algorithm well enough to really test it.
    1) Insert 6 rows beneath row 14
    2) AI15: =AI14-AH14
    3) AI16: =AI10 (copy into AI17 and AI18)
    4) Copy AI13:AI14 and paste into AI19:AI20.
    4a) J19: =J14 and copy into K19.
    4b) AH19: =AH13

    At this point AI15:AI20 look just like AI9:AI14 in your result sheet.

    5) Copy AI15:AI20 and paste going across row 15. When I do this, row 15 looks like row 9 in the result sheet and row 19 looks like row 13.

    Does that help at all?

  8. #8
    Registered User
    Join Date
    06-04-2017
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    5

    Re: Circular reference error

    Quote Originally Posted by MrShorty View Post
    4a) J19: =J14 and copy into K19.
    Should this be J20: =J14 and copy into K20? otherwise error #DIV/0! displays in row 20. I think you understand my overall calculation well enough but according to your direction, results are not exactly what I desire
    The problem is that inputted qty in row plan order must ensure that last month's ending stock can cover usage of next month. Anw, your guidance did help me have another aspect to deal with this kind of algorithm

    Actually, I don't focus on logic calculation, just simply want to record value which pops up in AJ13 after inputting a figure in AI9, then paste value from AJ13 into AJ9, continue the loop till the end of row
    So yesterday I tried using macro and excel wrote a copy-paste code for row 9 &row 14, then I multiplied that code and substituted rows address. After running macro, results show as my expectation.

    However, it merely works with around 10 item codes, if I extend writing code, excel notifies "procedure is too large". I am not a macro, VBA programmer, can you help me re-write code for hundreds items?
    Pls see attachment for your ref . I would be grateful for your support.
    Attached Files Attached Files

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

    Re: Circular reference error

    There's a lot to learn to go beyond the macro you have recorded to efficient coding. When you say that you are not a VBA programmer, does that also extend to other programming languages, or do you have experience in other programming languages.

    A couple of quick reads about improving recorded macros:
    This spends some time talking about getting rid of excess code in recorded macros: http://peltiertech.com/how-to-fix-a-recorded-macro/
    This one goes even further: http://www.tushar-mehta.com/excel/vb...rder/index.htm
    The latter link could also be interesting because he spends some time working with a copy/paste macro. He is working down columns A and AA, but the same basic idea could be used working across rows 14 and 9.

    One thing I would add to Tushar Meta's macro 11 is the idea of a loop to loop across the row. If you have any prior programming experience, you have probably encountered loops before. If you are new to any kind of programming, then learning loops will be a big part of your learning. One common loop structure: https://msdn.microsoft.com/en-us/lib.../gg251601.aspx

    I strongly suggest that you spend some time with those so that you understand how they work. From there I would expect something like this to do what you are describing (untested):
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-04-2017
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    5

    Re: Circular reference error

    Quote Originally Posted by MrShorty View Post

    I strongly suggest that you spend some time with those so that you understand how they work. From there I would expect something like this to do what you are describing (untested):
    Please Login or Register  to view this content.
    This code didn't work, it has error at the beginning "For columncount=35 to end". Can you kindly help me correct it? I don't have any proficient experience relating to macro and VBA, so dont know that it has many programming languages at all

    I have taken a look at tutorial links that you suggested, it seems to be helpful but quite hard for me to understand them thoroughly. I will make attempts to gain insights of macro, loop, vba to find out a better solution

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

    Re: Circular reference error

    it seems to be helpful but quite hard for me to understand them thoroughly.
    Yes, it can be difficult to learn these things when they are thrust on you like this. Keep studying, so that you can understand them. Here are a couple of simpler tutorials on For..Next loops that may help:
    http://www.excel-easy.com/vba/loop.html
    http://www.dummies.com/software/micr...xcel-2016-vba/

    The other important concept that you are probably struggling with is the range object (and the basic idea behind and object model). Here are a couple of introductions to Excel's object model:
    http://what-when-how.com/excel-vba/i...-object-model/
    http://www.dummies.com/software/micr...-object-model/

    This code didn't work, it has error at the beginning "For columncount=35 to end".
    I suspect that it doesn't like the "end" there. 35 is the starting column (column AI). You need to count to the last column and replace the "end" with that column number (40 or 50 or whatever this number needs to be).

+ 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 error
    By Nitro2481 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-10-2015, 08:04 AM
  2. [SOLVED] Circular Reference Error
    By jackleesteere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2014, 10:02 AM
  3. Circular Reference Error
    By jhclaws in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-10-2013, 05:52 AM
  4. Circular reference error
    By Riddling Lynx in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2008, 11:43 PM
  5. Circular Reference Error Even Though There Is No C.R.
    By MDW in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2006, 02:42 PM
  6. Circular reference error
    By Rick in forum Excel General
    Replies: 1
    Last Post: 10-05-2005, 04:05 PM
  7. [SOLVED] Circular reference error
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2005, 11:01 AM

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