Hi there, Please introduce a better way to solve my problem.

Asu · Apr 19, 2011

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]

Hi there, Please introduce a better way to solve my problem.

Asu

Board Regular

Excel Facts

Similar threads

Forum statistics

Share this page

Hi there, Please introduce a better way to solve my problem.

Asu

Board Regular

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock