Why am I getting a circular reference error?

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
Hi,

I'm having trouble figuring out how to revise this formula to get rid of the circular reference error Excel is telling me I have:

Code:
=IF(OR(F2="Y",AN2<=6),AQ2,IF(AND(O2="N",SEARCH("Open",D2),AQ2>=3),AQ2,IF(AND(O2="N",SEARCH("Open",D2)),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)<=3,AT2="Up"),VLOOKUP('WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP('WOs (DSP Only)'!A:B,2,FALSE)>=3,AT2="Up",AND(AS2=3,AT2="Up")),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)<=3,AT2="Up"),VLOOKUP('WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP('WOs (DSP Only)'!A:C,3,FALSE)>=3,AT2="Up",AND(AS2=3,AT2="Up")),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)<=3,AT2="Up"),VLOOKUP('WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP('WOs (DSP Only)'!A:D,4,FALSE)>=3,AT2="Up",AND(AS2=3,AT2="Up")),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)<=3,AT2="Up"),VLOOKUP('WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP('WOs (DSP Only)'!A:E,5,FALSE)>=3,AT2="Up",AND(AS2=3,AT2="Up")),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)<=3,AT2="Up"),VLOOKUP('WOs (DSP Only)'!A:F,6,FALSE),IF(AND(VLOOKUP('WOs (DSP Only)'!A:F,6,FALSE)>=3,AT2="Up",AND(AS2=3,AT2="Up")),3,IF(AND(AS2=2,AT2="Up"),2,AQ2))))))))))))))

I believe it has to do where within the formula I am trying to set AS equal to a number to test a scenario. Any thoughts on how to revise?

Thanks!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You haven't stated what cell the formula is in.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,928
Office Version
  1. 365
Platform
  1. MacOS
heres one issue, you can not refer to the same cell as the formula is in -
AND(AS2=3,AT2="Up") is in the formula a couple of times
check through the formula and look for all occurrences of AS2 as a cell reference and change ALL of those
 
Last edited:

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486

ADVERTISEMENT

So then, I'm trying to figure out how I can rewrite the formula where I have AS2=to something in the large nested if statement above. Because I'm trying to test AS2=3 in that statement multiple times. Can i just write =3? Any thoughts on how to rewrite?

This the line that needs help:
Code:
IF(AND(VLOOKUP('WOs (DSP Only)'!A:B,2,FALSE)>=3,AT2="Up",AND(AS2=3,AT2="Up")),3

Thanks!
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,928
Office Version
  1. 365
Platform
  1. MacOS
you can not have a formula in a cell and reference the same cell in the formula
hence the circular reference
AS3 cannot = 3 , as it will contain whatever the formula result is , but cannot give a result because its testing itself
 

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486

ADVERTISEMENT

Thanks etaf. I get that. I'm just trying to figure out a different approach.

For this line, from the full code above,
Rich (BB code):
IF(AND(VLOOKUP('WOs (DSP Only)'!A:B,2,FALSE)>=3,AT2="Up",AND(AS2=3,AT2="Up")),3


If I manually type in a "3" into AS2, everything still functions. I'm just trying to figure out how to do it with code.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,928
Office Version
  1. 365
Platform
  1. MacOS
where is the formula entered now? , it was in cell AS2 - so how can you enter a 3 into the cell if the formula is in it ?
not sure what you are trying to do now
we have answered this thread as to why you are getting a circular reference
 

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
You are correct, you have answered the circular logic question. Should I post a new thread for figuring out better logic? Sorry, I'm just frustrated with myself on not being able to figure this out.
 

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
Below is a sample file with my formula in L2, so you can see the whole picture. (I trimmed out columns so that is why the cells shifted from AS2 to L2).

Unique Key (A)Location ID (B)SID (C)Description (D)Designation (E)Currently Monitored (F)Test (G)Annual # of XXXX (H)Annual # of XXXX (I)Avg XXXX/XXXX (J)Current Days Between XXXX (K)Expected XXXX Per XXXX (L)Change In DBH (M)New Annual # of XXXX (N)Suggested Days Between Hauls (O)
Loc7508-2547404-40 Yards Open Top-TLoc7508254740440 OpenTNN4853.321.1180.00Up1821

<tbody>
</tbody>

Formula in column L:
Code:
=IF(OR(F2="Y",H2<=6),J2,IF(AND(G2="N",SEARCH("Open",D2),J2>=3),J2,IF(AND(G2="N",SEARCH("Open",D2)),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)<=3,M2="Up"),VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=3,M2="Up",AND(L2=3,M2="Up")),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)<=3,M2="Up"),VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=3,M2="Up",AND(L2=3,M2="Up")),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)<=3,M2="Up"),VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=3,M2="Up",AND(L2=3,M2="Up")),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)<=3,M2="Up"),VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=3,M2="Up",AND(L2=3,M2="Up")),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)<=3,M2="Up"),VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE),IF(AND(A2,VLOOKUP('WOs (DSP Only)'!A:F,6,FALSE)>=3,M2="Up",AND(L2=3,M2="Up")),3,IF(AND(L2=2,M2="Up"),2,J2))))))))))))))

Thanks!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,201
Messages
5,527,383
Members
409,759
Latest member
KCH

This Week's Hot Topics

Top