Using option buttons to direct vlookup at the correct table

[da_sprite]

I have 2 vlookup tables (A1:B2) and (A4:B5). The values in each cell are as follows:

A1=Male
A2=Female
B1=Man
B2=Woman

A4=Male
A5=Female
B4=Bull
B5=Cow

Cell A7 is where i will paste either the text "Male" or "Female" into. Cell A8 is where i wish to see the result displayed.

Question: How do i set up 2 option buttons (available from the control toolbox) with option1 labelled as "Human" and option2 as "Animal", such that:
- When i select the Human option and paste the text "Male" into cell A7, the result in cell A8 displays "Man"
Similarly:
-select Human option, paste "Female", result is "Woman"
-select Animal option, paste "Male", result is "Bull"
-select Animal option, paste Female, result is "Cow"

I'm new to excel's control toolbox, and am only able to implement the first part (the Human part) with vlookup by keying into cell A8 the following:
=VLOOKUP(A7,$A$1:$B$2,2,FALSE). Appreciate any help on how to use option buttons to point the vlookup function at the correct table.


Thank you

 

[venkat1926]

see the sample sheet below
A7 abdB7 have vadidations.. and
A8 has the formula.
change the validation in A7 adn B7 and see what happens to A8

A7 has validation under list as
human.animal
B7 has validation under list
a1:B2,A4:B5. I do not know whether this will be reflected in the html format sheet.

******** ******************** ************************************************************************><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table width="100%" align="center" border="0"><tbody><tr><td align="left">Microsoft Excel - da_sprite 1.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: xl2002 XP : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" width="100%" align="center" border="0"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td valign="center" align="right"><form name="formCb755237"><input *******="window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);" value="Copy Formula" name="btCb873980" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb078704"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name="sltNb935705"><option value="=VLOOKUP(A7,INDIRECT(B7),2,FALSE)" selected="selected">A8</option></select></td><td width="3%" align="right" bgcolor="#d4d0c8">=</td><td align="left" bgcolor="white"><input size="80" value="=VLOOKUP(A7,INDIRECT(B7),2,FALSE)" name="txbFb426622"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" width="2%" align="middle">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>A</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>1</center></td><td style="border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); border-width: 0.5pt; font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">male</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">man</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>2</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">female</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">woman</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>3</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>4</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">male</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">bull</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>5</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">female</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">cow</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>7</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">female</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">a4:B5</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>8</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">cow</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" width="100%" align="left"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; width: 120pt; background-color: rgb(255, 255, 255);" align="left">Sheet1</td><td> </td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>