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