If statement to avoid Creating Circular Reference

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

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.

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

"wjohnson" wrote:

>
> 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.
>
>
> --
> wjohnson
> ------------------------------------------------------------------------
> wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
> View this thread: http://www.excelforum.com/showthread...hreadid=494968
>
>

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