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

If statement to avoid Creating Circular Reference
 

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

If statement to avoid Creating Circular Reference
 

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

If statement to avoid Creating Circular Reference
 

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.


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=494968