If statement to avoid Creating Circular Reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim H - 20 Dec 2005 18:43 GMT

Good Day,

I have two columns of data With the following in each,

in Cell L2 and filled down is
=IF(H2=10,IF(U2=1,"$",1),IF(L3=1,IF(U2=1,"$",IF(W2=1,"Trl",1)),0))

In cell M2 and filled down is
=IF(I2=-10,IF(V2=1,"$",1),IF(M3=1,IF(V2=1,"$",IF(X2=1,"Trl",1)),0))

This works - but what I need to be able to do is find a way to add: While
the cell in one column has a value of 1, then the other cannot return a 1
until the value changes in the first changes

Any Ideas?

Best Regards,

Tim

Reply to this Message

wjohnson - 20 Dec 2005 21:53 GMT

Tim - Not exactly clear what you want to do but a couple of things I
think might be easier to accomplish.
=IF(H2=10,IF(U2=1,"$",1),IF(L3=1,IF(U2=1,"$",IF(W2 =1,"Trl",1)),0))
If you fill down - your second "IF" statement (L3=1) gets overwritten
when you copy down. Also you have U2=in there twice.
I would try using an AND statement something like the following:
=IF(AND(H2=10,U2=1),"$",IF(W2=1,"TRL",0))
In my limited function knowledge - your first "IF" statement should
have a "value" if "TRUE" and not another IF statement. Use the second
IF Statment for the "FALSE" part of the IF function - which is what my
example is trying to show you. If you use the AND function - when you
close it you will get an error message - just ignore it - go to the
formula area of the spreadsheet - put the cursor on the "IF" statement
you need to complete and then hit the "equal" sign.

Signature

wjohnson

Reply to this Message

Tim H - 21 Dec 2005 16:26 GMT

Thank you for your response, this formula was not written by me and I can
re-write it fo tread if,then,if,then,if,then,else. This, however, is not the
problem I am having, What I need to accomplish Is For both columns, while the
value in one is equal to 1, the value in the other cannot be 1. I Need to
find a way to do this for both columns without creating a circular reference.
Any Ideas?

Best Regards,

Tim Henning

> Tim - Not exactly clear what you want to do but a couple of things I
> think might be easier to accomplish.
[quoted text clipped - 10 lines]
> formula area of the spreadsheet - put the cursor on the "IF" statement
> you need to complete and then hit the "equal" sign.

Reply to this Message

wjohnson - 22 Dec 2005 05:37 GMT

Tim - Why not try having a cell on your spreadsheet like A1 referenc
the value in cell L2 and then make your M2 reference to cell A1 - so i
cell A1 is 1 - then M2 has to be something else. This should eliminat
the circular reference

Reply to this Message