Please help!!!!!!!!!!

[Asu]

I have a long [FONT=&quot]Coefficient [/FONT] table for calculation purpose according to different ratio with 7 columns.
I try to make it simple to show 3 of them only.



<table border="0" cellpadding="0" cellspacing="0" width="437"><col style="width: 45pt;" width="60"> <col style="width: 43pt;" width="57"> <col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl73" style="height: 12pt; width: 45pt;" width="60" height="16">
</td> <td class="xl74" style="width: 43pt;" width="57">
</td> <td class="xl74" style="width: 48pt;" width="64">
</td> <td colspan="4" class="xl80" style="border-right: 1pt solid black; border-left: medium none; width: 192pt;" width="256">[FONT=&quot]Coefficient [/FONT]</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl75" style="height: 12.75pt;" height="17">State</td> <td class="xl76">Location</td> <td class="xl76">Year</td> <td class="xl77" style="border-top: medium none;">a</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">b</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">c</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">d</td> </tr> <tr style="height: 12pt;" height="16"> <td rowspan="3" class="xl83" style="border-bottom: 1pt solid black; height: 36.75pt; border-top: medium none;" height="49">Ked</td> <td rowspan="3" class="xl86" style="border-bottom: 1pt solid black; border-top: medium none;">A Star</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">4</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt; border-top: medium none; border-left: medium none;" height="16">5</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">7</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none; border-left: medium none;" height="17">10</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">10</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">11</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">12</td> </tr> <tr style="height: 12pt;" height="16"> <td rowspan="3" class="xl83" style="border-bottom: 1pt solid black; height: 36.75pt; border-top: medium none;" height="49">F Tor</td> <td rowspan="3" class="xl86" style="border-bottom: 1pt solid black; border-top: medium none;">KL</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">22</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">33</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">44</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt; border-top: medium none; border-left: medium none;" height="16">5</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">55</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">66</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">77</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">88</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none; border-left: medium none;" height="17">10</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">1.1</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">1.2</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">1.3</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">1.4</td> </tr> <tr style="height: 12pt;" height="16"> <td rowspan="3" class="xl83" style="border-bottom: 1pt solid black; height: 36.75pt; border-top: medium none;" height="49">GH</td> <td rowspan="3" class="xl86" style="border-bottom: 1pt solid black; border-top: medium none;">JAK</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">0.1</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">0.2</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">0.3</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">0.4</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt; border-top: medium none; border-left: medium none;" height="16">5</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.5</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.6</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">0.7</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none; border-left: medium none;" height="17">10</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">9.1</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.001</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.002</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">0.003</td> </tr> </tbody></table>


Things I want it to be:
When I select the state 'Ked' from a drop-down list, the 'A Star' will automatically appear, and then I select the year I want, says '5', the Coefficient a,b,c,d will appear accordingly.


Example 1:

<table border="0" cellpadding="0" cellspacing="0" width="437"><col style="width: 45pt;" width="60"> <col style="width: 43pt;" width="57"> <col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt; width: 45pt;" width="60" height="16">
</td> <td class="xl67" style="width: 43pt;" width="57">
</td> <td class="xl67" style="width: 48pt;" width="64">
</td> <td colspan="4" class="xl77" style="border-right: 1pt solid black; border-left: medium none; width: 192pt;" width="256">Coefficient </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">State</td> <td class="xl69">Location</td> <td class="xl69">Year</td> <td class="xl70" style="border-top: medium none;">a</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">b</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">c</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">d</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="height: 12.75pt; border-top: medium none;" height="17">Ked</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">A Star</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">7</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">8</td> </tr> </tbody></table>

Example 2:

<table border="0" cellpadding="0" cellspacing="0" width="437"><tbody><tr style="height: 12pt;" height="16"><td class="xl66" style="height: 12pt; width: 45pt;" width="60" height="16">
</td> <td class="xl67" style="width: 43pt;" width="57">
</td> <td class="xl67" style="width: 48pt;" width="64">
</td> <td colspan="4" class="xl77" style="border-right: 1pt solid black; border-left: medium none; width: 192pt;" width="256">Coefficient </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">State</td> <td class="xl69">Location</td> <td class="xl69">Year</td> <td class="xl70" style="border-top: medium none;">a</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">b</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">c</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">d</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl75" style="height: 12.75pt; border-top: medium none;" height="17">F Tor</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">KL</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">11</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">22</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">33</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">44</td> </tr> </tbody></table>
The method 1 I used: this goes horizontally.
-The result is supposed to be either Example 1 or 2. The way I do is as follow;


state: Alt + D + L > List > select the state > done [this one works]
location: =VLOOKUP($E32,Source!C3:D6,2,0) [this one works]
year: Alt + D + L > List > select the year > done [this one works]
a: =VLOOKUP($F32:$G32,$C$3:$G$26,MATCH($H31,$C$2:$G$2,0),0)
Coefficient a : the result for 'a' shows #VALUE! [THIS ONE IS NOT WORKING & I STOPPED HERE]



[FONT=&quot]The method 2 I used: this goes vertically.[/FONT]
[FONT=&quot] the result is supposed to be: The way I do is as follow;[/FONT]

<table border="0" cellpadding="0" cellspacing="0" width="158" height="126"><col style="width: 45pt;" width="60"> <col style="width: 43pt;" width="57"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt; width: 45pt;" align="right" width="60" height="16">State =</td> <td class="xl65" style="width: 43pt;" width="57">F Tor</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt;" align="right" height="16">Location =</td> <td class="xl65">KL</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt;" align="right" height="16">Year =</td> <td class="xl65">2</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt;" align="right" height="16">a =</td> <td class="xl65">11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt;" align="right" height="16">b =</td> <td class="xl65">22</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt;" align="right" height="16">c =</td> <td class="xl65">33</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl66" style="height: 12pt;" align="right" height="16">d =</td> <td class="xl65">44</td> </tr> </tbody></table> [FONT=&quot]
[/FONT]
[FONT=&quot]State: Alt + D + L > List > select the state > done [this one works][/FONT]
[FONT=&quot]Location: =VLOOKUP($E32,Source!C3:D6,2,0) [this one works][/FONT]
[FONT=&quot]Year: Alt + D + L > List > select the year > done [this one works][/FONT]
[FONT=&quot]a: =VLOOKUP($F32:$G32,$C$3:$G$26,MATCH($H31,$C$2:$G$2,0),0)[/FONT]
[FONT=&quot]Coefficient a : the result for 'a' shows #N/A [THIS ONE IS NOT WORKING & I STOPPED HERE][/FONT]


[FONT=&quot]My problem is, I can do up to 'year' only and can’t go further. Could anyone tell me how to do this? Perhaps, I use the wrong way. Please correct me if I do.[/FONT]
[FONT=&quot]I hope I made this clear. Do ask me if there is something I make you confusing. Thank you :D[/FONT]

 

[Joe4]

Duplicate: http://www.mrexcel.com/forum/showthread.php?t=544579

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).