Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Home > English site > Articles > Circular References
Deze pagina in het Nederlands

Working with Circular references in Excel

Introduction

If you've come to this page, chances are you've experienced the "Circular reference warning" popping up when you opened an Excel file or entered a formula. Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I'll try to demystify that message here!

You may also find an excerpt of parts of the article on the Microsoft Office blog

The next couple of pages discuss:

Table Of Contents


Comments

Showing last 8 comments of 20 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (12/4/2013 4:17:13 PM)

Hi Brian,

What happens if you hit F9, does it then update to the proper value?
Perhaps the number of iterations is incorrect?

 


Comment by: Brian (12/5/2013 2:28:05 AM)

Hello Jan,

Hitting the F9 key does not seem to have any affect. The iterations are set to 1 as it is a large spreadsheet and once a value is locked in it needs to stay until the next day. The circular references are the result of if/then formulas.

Thanks,

Brian

 


Comment by: Jan Karel Pieterse (12/5/2013 7:49:10 AM)

Hi Brian,

OK. In that case I'm afraid I cannot really help without having your workbook.

 


Comment by: richie (1/21/2014 4:14:09 AM)

anyone help me,always shown #N/A





=VLOOKUP(B8,CHOOSE(VLOOKUP(A8,Sheet3!F2:G36,2,FALSE),Sheet3!B2:C4,Sheet3!B6:C8,Sheet3!B10:C12,Sheet3!B14:C16,Sheet3!B18:C20,Sheet3!B22:C24,Sheet3!B26:C28,Sheet3!B30:C32,Sheet3!B34:C36,Sheet3!B38:C40,Sheet3!B42:C44,Sheet3!B46:C48,Sheet3!B50:C52,Sheet3!B54:C56,Sheet3!B58:C60,Sheet3!B62:C64,Sheet3!B66:C68,Sheet3!B70:C72,Sheet3!B74:C76,Sheet3!B78:C80,Sheet3!B82:C84,Sheet3!B86:C88,Sheet3!B90:C92,Sheet3!B94:C96,Sheet3!B98:C100,Sheet3!B112:C114,Sheet3!B116:C118),2,FALSE)

 


Comment by: Jan Karel Pieterse (1/21/2014 6:13:07 AM)

Hi Richie,

What is wrong with a simple:

=VLOOKUP(B8,Sheet3!B2:C118,2,FALSE)

I suspect the structure of your worksheet is making it hard to find what you need for your calculation...

 


Comment by: richie (2/8/2014 7:44:13 AM)

Jan Karel Pieterse

hope you don't mind, can you give me your email add!,then i'll send you my format...

=)

 


Comment by: Chris (4/16/2014 7:15:59 AM)

Hi Jan, I have i hope a basic question .
wish to input a value into a cell and multiply it by another cells value, but have the result show in the first cell.

b1 = a1*b1 . so that the formula is just =a1* the value that will be entered into bi at another time. i'd like not to have to enter the value in the formula bar but in the cell, if at all possible.

this is to create a daily calories calculator

regards chris

 


Comment by: Jan Karel Pieterse (4/16/2014 12:09:57 PM)

Hi Chris,

Why not show the result in a different cell than the input cell? That is the better aproach because you'll be able to see both input value and result.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].