I'm looking for a function whic will change text to an assigned value depending on what text it is (so I can go back later with the CHOOSE function and use the #s). Sorry if this is a stupid question ....I'm kinda new at this.
Converting Text To Assigned Value?
-
-
Hi - welcome to the board!
"will change text to an assigned value depending on what text it is "
...care to post back with the details about what you are actually trying to do - examples really help!
paddy
-
If I enter in lets say....Airplane, either in that cell or another cell somewhere else I want it to put its "assigned value", lets say it is 1, If I enter Balloon, its assigned value could be 2 and that would appear instead of the one. See then I can go in and use CHOOSE in functions and depending on the number it will use different formulas
-
vlookup()'s what you need. check it out in the help file & see example below:
<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book5</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><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 <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb724366><INPUT onclick='window.clipboardData.setData("Text",document.formFb049879.sltNb288580.value);' type=button value="Copy Formula" name=btCb259793></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6><TABLE border=0><TBODY><TR><FORM name=formFb049879><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb049879.txbFb195777.value = document.formFb049879.sltNb288580.value" name=sltNb288580><OPTION value==VLOOKUP(E1,A1:B5,2,0) selected>E2</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value==VLOOKUP(E1,A1:B5,2,0) name=txbFb195777></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Ref</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Value</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Lookup</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Car</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Airplane</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> 1 </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Return</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"><A onclick="document.formFb049879.sltNb288580.options[0].selected=true; document.formFb049879.txbFb195777.value = document.formFb049879.sltNb288580.value;" href="#javascript:void(0);">3</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Baloon</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> 2 </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Car</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> 3 </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Boat</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> 4 </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=6><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>
the formula is
=VLOOKUP(E1,A1:B5,2,0)
HTH
paddy
-
Sedso10,
A function can only effect the content of the cell it is enetered in. If you were to type anything else in that cell it would type over and delete the function.
For your situation I would recommend a VLOOKUP function working from a list that would return a numerical value NEXT to the cell that you enter "airplane" in.
It works like
=VLOOKUP(A11,A1:B6,2,0)
where A11 contain the value you want to lookup - in this case airplane.
A1:B6 is the range that has the list of criteria and values
2 is the column to return - in this case the column containing the numbers
0 means you want an exact match - 99% rec.HTH
Weasel -
-
:cheers:
sry Paddyd wasnt trying to butt in
-
butt in all you like - the more the merrier!
-
Thanks Everyone for ya'lls help
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!