Finding information from a matrix

[Dan190984]

Hi all

I have a matrix table which looks as follows....

<TABLE style="WIDTH: 174pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=232><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" span=6 width=28><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 48pt; HEIGHT: 41.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=55 rowSpan=2 width=64>Away



Home
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 42pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=56 colSpan=2>Bournemouth</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 42pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=56 colSpan=2>Brentford </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 42pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=56 colSpan=2>Brighton</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl70 height=21 width=28>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=28>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl70 width=28>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=28>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl70 width=28>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=28>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=64>Bournemouth</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 width=28>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 width=28>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=28></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl64 height=21 width=64>Brentford</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl64 height=21 width=64>Brighton</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=28></TD></TR></TBODY></TABLE>

and a seperate sheet which looks as follows.

<TABLE style="WIDTH: 225pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=297><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" span=4 width=58><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 49pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 height=34 width=65>H</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 176pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 width=232 colSpan=4>Bournemouth</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 49pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl72 height=34 width=65>A</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 176pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 width=232 colSpan=4>Brentford</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 49pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl71 height=34 width=65>Score (H/A)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 88pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=116 colSpan=2></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 88pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=116 colSpan=2></TD></TR></TBODY></TABLE>

Basically what I want to happen is to be able to enter a different home and away team and then the Score (H/A) section autofill.

The score (H/A) has two cells although this does not show well in what I have copied

How would I do this?

Thanks

 

[Matty]

Hi,

I'm not sure whether it's the way your matrix has come out via the forum software, but it looks like you have some merged Cells here. Merged Cells are the devil's work in Excel. Consider 'Center Across Selection' instead, which can be found under Format Cells > Alignment > Horizontal.

Another issue to overcome is the fact that once the Cells are unmerged, your Away team will only occupy the Cell above 'H'. This isn't ideal, but we can get around it.

The following couple of formulas should do the trick:

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Away</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Bournemouth</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brentford</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brighton</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Home</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">H</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">A</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">H</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">A</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">H</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Bournemouth</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="font-family: Calibri; text-align: right;">3</td><td style="font-family: Calibri; text-align: right;">1</td><td style="font-family: Verdana;">
</td><td style="font-family: Verdana;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brentford</td><td style="font-family: Verdana;">
</td><td style="font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="font-family: Verdana; text-align: right;">5</td><td style="font-family: Verdana; text-align: right;">4</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brighton</td><td style="font-family: Verdana; text-align: right;">6</td><td style="font-family: Verdana; text-align: right;">4</td><td style="font-family: Verdana;">
</td><td style="font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">H</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brighton</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">A</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Bournemouth</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Score (H/A)</td><td style="background-color: rgb(204, 255, 204); font-family: Verdana; text-align: right;">6</td><td style="background-color: rgb(204, 255, 204); font-family: Verdana; text-align: right;">4</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B10</td><td>=INDEX(B3:G5,MATCH(B8,A3:A5,0),MATCH(B9,B1:G1,0))</td></tr><tr><td>C10</td><td>=INDEX(B3:G5,MATCH(B8,A3:A5,0),MATCH(B9,B1:G1,0)+1)</td></tr></tbody></table></td></tr></tbody></table>
Notice how the second MATCH in the formula in C10 has +1 added - this is to get around empty Cell problem.

Hope this helps.

Matty