Why am I getting a circular reference error?

[nirvehex]

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:

=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!

 

[MARK858]

You haven't stated what cell the formula is in.

 

[nirvehex]

You haven't stated what cell the formula is in.

Sorry, the formula is in cell AS2.

Thanks again!

 

[etaf]

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

 

[nirvehex]

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:

IF(AND(VLOOKUP('WOs (DSP Only)'!A:B,2,FALSE)>=3,AT2="Up",AND(AS2=3,AT2="Up")),3

Thanks!

 

[etaf]

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]

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

For this line, from the full code above,

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]

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]

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]

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).

[TABLE="width: 1886"]
<tbody>[TR]
[TD]Unique Key (A)[/TD]
[TD]Location ID (B)[/TD]
[TD]SID (C)[/TD]
[TD]Description (D)[/TD]
[TD]Designation (E)[/TD]
[TD]Currently Monitored (F)[/TD]
[TD]Test (G)[/TD]
[TD]Annual # of XXXX (H)[/TD]
[TD]Annual # of XXXX (I)[/TD]
[TD]Avg XXXX/XXXX (J)[/TD]
[TD]Current Days Between XXXX (K)[/TD]
[TD]Expected XXXX Per XXXX (L)[/TD]
[TD]Change In DBH (M)[/TD]
[TD]New Annual # of XXXX (N)[/TD]
[TD]Suggested Days Between Hauls (O)[/TD]
[/TR]
[TR]
[TD]Loc7508-2547404-40 Yards Open Top-T[/TD]
[TD]Loc7508[/TD]
[TD]2547404[/TD]
[TD]40 Open[/TD]
[TD]T[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]48[/TD]
[TD]53.32[/TD]
[TD]1.11[/TD]
[TD]8[/TD]
[TD]0.00[/TD]
[TD]Up[/TD]
[TD]18[/TD]
[TD]21[/TD]
[/TR]
</tbody>[/TABLE]

Formula in column L:

=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!