conditional link circularity

  • I would like to be able to test a cell with- if -and return a value from another cell(sheet). Is there a way around circularity here? I am not VBA literate. Barely excel literate. if A1=1,A1= sheet2(B1), A1=0

    TIA BOB

  • going literally by what you've asked, you cannot have a value and a formula in a cell.


    You would need VBA to get anything close to this. Are you interested in that?

  • I want to take a value from one worksheet(W1) and place it in another(W2) based on a conditional. In order to do that I need to reference the if from a different cell and return value to still another cell?? 3 cells at least??
    if W2(a1)=1,W2(a2)=W1(b1), W2(a2)=0 Does that work??

    TIA BOB

  • You need 1 extra cell. This is the cell that will hold the value that the condition is based upon. Here's an example. Instead of having the value "Denis" on another sheet, I have put it on this sheet for the sake of ease:


    <CENTER&gt;<TABLE borderColor=#c0c0c0 cellSpacing=0 width="60%" align=center border=1&gt;<TBODY&gt;<TR&gt;<TD bgColor=#0c266b colSpan=5&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;<B&gt;Microsoft Excel - Book1</B&gt;</FONT&gt;</TD&gt;<TD align=right&gt;<FONT color=white size=2&gt;___Running: xl2000 : OS = Windows NT 4 </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD bgColor=#d4d0c8 colSpan=5&gt;<TABLE height=10 width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb7552&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb3707.sltNb8739.value);' type=button value="Copy Formula" name=btCb8704&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD bgColor=white colSpan=5&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb3707&gt;<TD align=middle width="5%" bgColor=white&gt;<SELECT onchange="document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value" name=sltNb8739&gt;<OPTION value="=IF(A1 = 1, A2, 0)" selected&gt;B1</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=120 value="=IF(A1 = 1, A2, 0)" name=txbFb8093&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<BR&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;A</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;B</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;C</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;D</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;1</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=right bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;1</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb3707.sltNb8739.options[0].selected=true; document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;Denis</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;Denis</FONT&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;3</CENTER&gt;</B&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD colSpan=5&gt;<U&gt;Sheet1</U&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;<BR&gt;<BR&gt;<FONT color=#339966 size=1&gt;The above image was automatically generated by [HtmlMaker 2.12] </FONT&gt;<FONT color=#339966 size=1&gt;If you want this FREE SOFT, <A href="http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm"&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT color=#339966 size=1&gt;This code was graciously allowed to be modified: by <A href="mailto:[email protected]"&gt;Ivan F Moala</A&gt; All credit to <A href="mailto:[email protected]"&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!