Why am I getting a circular reference error?

nirvehex

Active Member
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!

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
You haven't stated what cell the formula is in.

nirvehex

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

Sorry, the formula is in cell AS2.

Thanks again!

etaf

Well-known Member
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

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

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
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
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
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-T Loc7508 2547404 40 Open T N N 48 53.32 1.11 8 0.00 Up 18 21

<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:

Replies
5
Views
325
Replies
5
Views
383
Replies
0
Views
208
Replies
1
Views
266
Replies
1
Views
234