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

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:

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