Offset Voodoo

[agutts6]

I'm a noob to the machinations of offset. Specifically... using cell references within it.

<table width="448" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="7"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td style="width: 48pt;" width="64">Opp 1</td> <td style="width: 48pt;" width="64">Loc</td> <td style="width: 48pt;" width="64">Opp 2</td> <td style="width: 48pt;" width="64">Loc</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">Odds</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MIL</td> <td>ATL</td> <td>HFA</td> <td>PHI</td> <td>Non-HF</td> <td>
</td> <td>HELP!!!</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MIL</td> <td>ARI
</td> <td>HFA</td> <td>SF</td> <td>HFA</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MIL</td> <td>ATL</td> <td>HFA</td> <td>ARI</td> <td>HFA</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PHI</td> <td>SF</td> <td>HFA</td> <td>MIL</td> <td>HFA</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PHI</td> <td>SF</td> <td>HFA</td> <td>ATL</td> <td>HFA</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PHI</td> <td>ARI</td> <td>HFA</td> <td>ATL</td> <td>HFA</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PHI</td> <td>ARI</td> <td>HFA</td> <td>MIL</td> <td>HFA</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ATL</td> <td>MIL</td> <td>Non-HF</td> <td>PHI</td> <td>Non-HF</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ATL</td> <td>MIL</td> <td>Non-HF</td> <td>SF</td> <td>Non-HF</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ATL</td> <td>MIL</td> <td>Non-HF</td> <td>ARI</td> <td>Non-HF</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>PHI</td> <td>SF</td> <td>MIL</td> <td>ATL</td> <td>ARI</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HFAPHI</td> <td class="xl65">--</td> <td class="xl65" align="right">62.0%</td> <td class="xl65" align="right">66.2%</td> <td class="xl65" align="right">58.6%</td> <td class="xl65" align="right">70.9%</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HFASF</td> <td class="xl65" align="right">43.6%</td> <td class="xl65">--</td> <td class="xl65" align="right">57.3%</td> <td class="xl65" align="right">49.3%</td> <td class="xl65" align="right">62.4%</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HFAMIL</td> <td class="xl65" align="right">39.2%</td> <td class="xl65" align="right">48.3%</td> <td class="xl65">--</td> <td class="xl65" align="right">44.8%</td> <td class="xl65" align="right">58.1%</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HFAATL</td> <td class="xl65" align="right">47.0%</td> <td class="xl65" align="right">56.3%</td> <td class="xl65" align="right">60.7%</td> <td class="xl65">--</td> <td class="xl65" align="right">65.7%</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HFAARI</td> <td class="xl65" align="right">34.3%</td> <td class="xl65" align="right">43.1%</td> <td class="xl65" align="right">47.5%</td> <td class="xl65" align="right">39.7%</td> <td class="xl65">--</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>PHI</td> <td>SF</td> <td>MIL</td> <td>ATL</td> <td>ARI</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Non-HFPHI</td> <td class="xl65">--</td> <td class="xl65" align="right">56.4%</td> <td class="xl65" align="right">60.8%</td> <td class="xl65" align="right">53.0%</td> <td class="xl65" align="right">65.7%</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Non-HFSF</td> <td class="xl65" align="right">38.0%</td> <td class="xl65">--</td> <td class="xl65" align="right">51.7%</td> <td class="xl65" align="right">43.7%</td> <td class="xl65" align="right">56.9%</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Non-HFMIL</td> <td class="xl65" align="right">33.8%</td> <td class="xl65" align="right">42.7%</td> <td class="xl65">--</td> <td class="xl65" align="right">39.3%</td> <td class="xl65" align="right">52.5%</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Non-HFATL</td> <td class="xl65" align="right">41.4%</td> <td class="xl65" align="right">50.7%</td> <td class="xl65" align="right">55.2%</td> <td class="xl65">--</td> <td class="xl65" align="right">60.3%</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Non-HFARI</td> <td class="xl65" align="right">29.1%</td> <td class="xl65" align="right">37.6%</td> <td class="xl65" align="right">41.9%</td> <td class="xl65" align="right">34.3%</td> <td class="xl65">--</td> <td class="xl65">
</td> </tr> </tbody></table>


(Will this display okay? Hmppph.)

Anyway.... where I typed 'HELP!!' is where I'm trying to plug my offset in. What I'd like to do.... is use a concatenation of the Left-most column and first 'Loc' column (so, for the top row, "MILHFA") which can be used with the 'Opp 1' column to reference things in the lower table, by opponent. (So... the interesection "MILHFA" and "ARI" returns the appropriate %, as would the intersection of "MILHFA" and "ATL")

Am I describing this okay? MMM. Alright now.

--Bilberg

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">Opp 1</td><td style="text-align: center;;">Loc</td><td style="text-align: center;;">Opp 2</td><td style="text-align: center;;">Loc</td><td style="text-align: center;;"></td><td style="text-align: center;;">Odds</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;">PHI</td><td style="text-align: center;;">Non-HF</td><td style="text-align: center;;"></td><td style="text-align: center;;">44.8%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">ARI</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;">SF</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;"></td><td style="text-align: center;;">58.1%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;">ARI</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;"></td><td style="text-align: center;;">44.8%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">PHI</td><td style="text-align: center;;">SF</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;"></td><td style="text-align: center;;">62.0%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">PHI</td><td style="text-align: center;;">SF</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;"></td><td style="text-align: center;;">62.0%</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">PHI</td><td style="text-align: center;;">ARI</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;"></td><td style="text-align: center;;">70.9%</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">PHI</td><td style="text-align: center;;">ARI</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">HFA</td><td style="text-align: center;;"></td><td style="text-align: center;;">70.9%</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">Non-HF</td><td style="text-align: center;;">PHI</td><td style="text-align: center;;">Non-HF</td><td style="text-align: center;;"></td><td style="text-align: center;;">55.2%</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">Non-HF</td><td style="text-align: center;;">SF</td><td style="text-align: center;;">Non-HF</td><td style="text-align: center;;"></td><td style="text-align: center;;">55.2%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">Non-HF</td><td style="text-align: center;;">ARI</td><td style="text-align: center;;">Non-HF</td><td style="text-align: center;;"></td><td style="text-align: center;;">55.2%</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: center;;">PHI</td><td style="text-align: center;;">SF</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">ARI</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">HFAPHI</td><td style="text-align: center;;">--</td><td style="text-align: center;;">62.0%</td><td style="text-align: center;;">66.2%</td><td style="text-align: center;;">58.6%</td><td style="text-align: center;;">70.9%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">HFASF</td><td style="text-align: center;;">43.6%</td><td style="text-align: center;;">--</td><td style="text-align: center;;">57.3%</td><td style="text-align: center;;">49.3%</td><td style="text-align: center;;">62.4%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">HFAMIL</td><td style="text-align: center;;">39.2%</td><td style="text-align: center;;">48.3%</td><td style="text-align: center;;">--</td><td style="text-align: center;;">44.8%</td><td style="text-align: center;;">58.1%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">HFAATL</td><td style="text-align: center;;">47.0%</td><td style="text-align: center;;">56.3%</td><td style="text-align: center;;">60.7%</td><td style="text-align: center;;">--</td><td style="text-align: center;;">65.7%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">HFAARI</td><td style="text-align: center;;">34.3%</td><td style="text-align: center;;">43.1%</td><td style="text-align: center;;">47.5%</td><td style="text-align: center;;">39.7%</td><td style="text-align: center;;">--</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;"></td><td style="text-align: center;;">PHI</td><td style="text-align: center;;">SF</td><td style="text-align: center;;">MIL</td><td style="text-align: center;;">ATL</td><td style="text-align: center;;">ARI</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">Non-HFPHI</td><td style="text-align: center;;">--</td><td style="text-align: center;;">56.4%</td><td style="text-align: center;;">60.8%</td><td style="text-align: center;;">53.0%</td><td style="text-align: center;;">65.7%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">Non-HFSF</td><td style="text-align: center;;">38.0%</td><td style="text-align: center;;">--</td><td style="text-align: center;;">51.7%</td><td style="text-align: center;;">43.7%</td><td style="text-align: center;;">56.9%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">Non-HFMIL</td><td style="text-align: center;;">33.8%</td><td style="text-align: center;;">42.7%</td><td style="text-align: center;;">--</td><td style="text-align: center;;">39.3%</td><td style="text-align: center;;">52.5%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">Non-HFATL</td><td style="text-align: center;;">41.4%</td><td style="text-align: center;;">50.7%</td><td style="text-align: center;;">55.2%</td><td style="text-align: center;;">--</td><td style="text-align: center;;">60.3%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">Non-HFARI</td><td style="text-align: center;;">29.1%</td><td style="text-align: center;;">37.6%</td><td style="text-align: center;;">41.9%</td><td style="text-align: center;;">34.3%</td><td style="text-align: center;;">--</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=INDEX(<font color="Blue">B$15:F$26,MATCH(<font color="Red">C2&A2,A$15:A$26,0</font>),MATCH(<font color="Red">B2,B$14:F$14,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[agutts6]

Did you ever know that you're my hero?
You're everything I wish I could be.
And I can fly higher than an eagle.
And you are the wind beneath my wings.

--Amy Grant, I think... I don't know, my Mom used to play it around the house when she was cleaning.

 

[markmzz]

Did you ever know that you're my hero?
You're everything I wish I could be.
And I can fly higher than an eagle.
And you are the wind beneath my wings.

--Amy Grant, I think... I don't know, my Mom used to play it around the house when she was cleaning.

I'm happy that your problem was solved.

And thank you for the feedback.

Markmzz

 

[zakasnak]

Did you ever know that you're my hero?
You're everything I wish I could be.
And I can fly higher than an eagle.
And you are the wind beneath my wings.

--Amy Grant, I think... I don't know, my Mom used to play it around the house when she was cleaning.

Bette Midler. j

http://www.youtube.com/watch?v=c9ZMDPf9hZw