Circular Reference Error Help

[Ancient Wolf]

I really need some help on this one.

I'm working on a sheet that pulls up product numbers through a series of questions, and places the numbers in specific cells such as C170, and C171. It then uses the numbers in C170 and C171 to pull up the price of those products and place them in E170 and E171. Now, if a customer is going to have more than one product, they will get a special price on the second item depending upon which one costs less. So I then needed a formula, which I placed in G170, that would compare the prices. I created one that will determine which price was lower and then return a single letter in response for now. X if E170 > E171, Y if E170 < E171, and Z if E170 = E171. I then need my original formulas in C170 and C171 to look at G170 and determine which one if either should return the special product number 798. That is where the circular reference error comes in.

Does anyone know how I can fixe the error and still have it do as I described?
Here are the formulas I am using. I can't place the whole formulas that are in C170 and C171 because they are far too long.

C170: IF(G170="y","798"

C171: IF(OR(K171="x",K171="z"),"798",

E170 and E171: =IF(ISERROR(INDEX(Tables!BB2:BF185,MATCH(B171,Tables!BB2:BB185,0),K5)),0,INDEX(Tables!BB2:BF185,MATCH(B171,Tables!BB2:BB185,0),K5))

G170: =IF(AND(E170>E171,E171<>0),"x",IF(AND(E170<E171,E170<>0),"y",IF(AND(E170=E171,E170<>0,E171<>0),"z","")))

 

[Andrew Poulsom]

Those formulas don't create a circular reference. Something else must be causing it.

 

[JackDanIce]

Ignore my post, see Andrew's reply

 

[Ancient Wolf]

Perhpas not alone, but together? I know that if I take out the formulas that retrieve the pricing, that the circle is broken. Same thing if I remove the section of formula in question at C170 and C171.

It looked to me the error happens because of this. E170 and E171 are dependant on C170 and C171 for the price. G170 is dependant on E170 and E171 to determine which costs more. The circle is completed when C170 and C171 looks to G170 to determine which will use code 798.

I can't just simply allow iterative calculations because I am not the only person to use the spreadsheet.

 

[Andrew Poulsom]

In what way are E170 and E171 dependent on C170 and C171? They only refer to B170 and B171.

 

[Ancient Wolf]

I'm really sorry. When I type out examples I try not use too many specifics straight from the sheet I'm working on. When I pasted in the formula I guess I forgot to change the B to C.

 

[Andrew Poulsom]

Please post your actual formulas.

 

[Ancient Wolf]

B171: IF(AND(B26=delo,K171="y"),"798"
B172 IF(AND(B26=delo,OR(K171="x",K171="z")),"798"
E171: =IF(ISERROR(INDEX(Tables!BB2:BF185,MATCH(B171,Tables!BB2:BB185,0),K5)),0,INDEX(Tables!BB2:BF185,MATCH(B171,Tables!BB2:BB185,0),K5))
K171: =IF(AND(E171>E172,E172<>0),"x",IF(AND(E171<E172,E171<>0),"y",IF(AND(E171=E172,E171<>0,E172<>0),"z","")))

I'm sorry for causing problems.

 

[Andrew Poulsom]

Well you can't lookup B171 in E171, then refer to E171 in K171 and K171 in B171. That's the circle.

Maybe there's a solution if you explain what you want to do in words.

 

[Ancient Wolf]

I forgot to thank you Andrew Poulsom for your quick replies. I was looking for a way to making the formulas work as they are, but I think I have decided on an alternate solution.

So thank you again Andrew for your help. I'll be back if my alternative doesn't work.