Excel table 2007 use indirect function in a row or column reference

[Roxexcel]

Hello Excel Experts,

I am trying to use the indirect function in a table reference to return the value at the intersection of the row and column, like so:
=Table5[#This Row] Table5[indirect(C7)]

However despite many variations or attempts with the match formula, this isn't working. The lovely excel popup box with "the formula you typed contains an error" appears every time. It then returns to highlight the second instance of "Table 5" in the formula.

I need get this result in numerous situations in my workbook and I am hoping someone out there has an alternative solution.

You can see an example of the table below:
<table style="border-collapse: collapse; width: 281pt;" width="375" border="0" cellpadding="0" cellspacing="0"><col style="width: 77pt;" width="103"> <col style="width: 44pt;" width="59"> <col style="width: 16pt;" width="21"> <col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 77pt;" width="103" height="20">Select Scenario:</td> <td class="xl66" style="width: 44pt;" width="59">Season</td> <td style="width: 16pt;" width="21">
</td> <td style="width: 48pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="64">State</td> <td style="width: 48pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="64">Fruit</td> <td style="width: 48pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 1.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="64">Season</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="center">#VALUE!</td> <td>
</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">ACT</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">Apple</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(184, 204, 228);">Winter</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>NSW</td> <td>
</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">NSW</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">Pear</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(219, 229, 241);">Spring</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>NT</td> <td>
</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">NT</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">Orange</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(184, 204, 228);">Summer</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>SA</td> <td>
</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">SA</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">Banana</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(219, 229, 241);">Autumn</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>QLD</td> <td>
</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">QLD</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">-</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(184, 204, 228);">-</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>TAS</td> <td>
</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">TAS</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">-</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(219, 229, 241);">-</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>VIC</td> <td>
</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">VIC</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">-</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; background: none repeat scroll 0% 0% rgb(184, 204, 228);">-</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>WA</td> <td>
</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color white -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">WA</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color white -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">-</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; background: none repeat scroll 0% 0% rgb(219, 229, 241);">-</td> </tr> </tbody></table>
"Season" is a data validation list that includes state, fruit and season. When this value is changed I want the list of values below it to update according to the values in table 5.

The formula works if i specify the column, but then as you can see, when the scenario changes, the values don't.

Your thoughts/ideas are appreciated!

Roxanne

 

[AlphaFrog]

There are different ways this could be done. This is one way...

B2 is a drop-down list

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.8px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Verdana; ">Season</td><td style="font-family:Verdana; ">*</td><td style="background-color:#666699; color:#ffffff; font-weight:bold; font-family:Calibri; font-size:11pt; ">State</td><td style="background-color:#666699; color:#ffffff; font-weight:bold; font-family:Calibri; font-size:11pt; ">Fruit</td><td style="background-color:#666699; color:#ffffff; font-weight:bold; font-family:Calibri; font-size:11pt; ">Season</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Verdana; text-align:center; ">Winter</td><td style="font-family:Verdana; ">*</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">ACT</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">Apple</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">Winter</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Verdana; text-align:center; ">Spring</td><td style="font-family:Verdana; ">*</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">NSW</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">Pear</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">Spring</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Verdana; text-align:center; ">Summer</td><td style="font-family:Verdana; ">*</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">NT</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">Orange</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">Summer</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Verdana; text-align:center; ">Autumn</td><td style="font-family:Verdana; ">*</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">SA</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">Banana</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">Autumn</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Verdana; text-align:center; ">-</td><td style="font-family:Verdana; ">*</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">QLD</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">-</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">-</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Verdana; text-align:center; ">-</td><td style="font-family:Verdana; ">*</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">TAS</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">-</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">-</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Verdana; text-align:center; ">-</td><td style="font-family:Verdana; ">*</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">VIC</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">-</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">-</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Verdana; text-align:center; ">-</td><td style="font-family:Verdana; ">*</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">WA</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">-</td><td style="background-color:#ccccff; font-family:Calibri; font-size:11pt; ">-</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B3</td><td >=INDEX($D$2:$F$10,ROW<span style=' color:008000; '>(2:2)</span>,MATCH<span style=' color:008000; '>($B$2,$D$2:$F$2,0)</span>)</td></tr><tr><td >B4</td><td >=INDEX($D$2:$F$10,ROW<span style=' color:008000; '>(3:3)</span>,MATCH<span style=' color:008000; '>($B$2,$D$2:$F$2,0)</span>)</td></tr><tr><td >B5</td><td >=INDEX($D$2:$F$10,ROW<span style=' color:008000; '>(4:4)</span>,MATCH<span style=' color:008000; '>($B$2,$D$2:$F$2,0)</span>)</td></tr><tr><td >B6</td><td >=INDEX($D$2:$F$10,ROW<span style=' color:008000; '>(5:5)</span>,MATCH<span style=' color:008000; '>($B$2,$D$2:$F$2,0)</span>)</td></tr><tr><td >B7</td><td >=INDEX($D$2:$F$10,ROW<span style=' color:008000; '>(6:6)</span>,MATCH<span style=' color:008000; '>($B$2,$D$2:$F$2,0)</span>)</td></tr><tr><td >B8</td><td >=INDEX($D$2:$F$10,ROW<span style=' color:008000; '>(7:7)</span>,MATCH<span style=' color:008000; '>($B$2,$D$2:$F$2,0)</span>)</td></tr><tr><td >B9</td><td >=INDEX($D$2:$F$10,ROW<span style=' color:008000; '>(8:8)</span>,MATCH<span style=' color:008000; '>($B$2,$D$2:$F$2,0)</span>)</td></tr><tr><td >B10</td><td >=INDEX($D$2:$F$10,ROW<span style=' color:008000; '>(9:9)</span>,MATCH<span style=' color:008000; '>($B$2,$D$2:$F$2,0)</span>)</td></tr></table></td></tr></table>

 

[Roxexcel]

Spectacular!!! This works a dream! Really something I should have thought of myself but I guess it was just one of those days!

As a note, I had remove the data from being a table and take it back to standard data for it to work. (though didn't try very hard to see if it would work as is).

Thanks AlphaFrog!

 

[Roxexcel]

OH NO! I have unfortunately encountered the same problem again, but I can't adjust this formula to work... Once again if I could use the indirect function or a cell reference to indicate what header I wan't to refer to in a table, this would work.

The situation
Now that I have these row headers appearing correctly, I now need to refer to another table to pull up some results.

I know that a pivot table will easily do this, however I need to do further analysis to the data that won't work with a pivot.

<table style="border-collapse: collapse; width: 253pt;" width="337" border="0" cellpadding="0" cellspacing="0"><col style="width: 50pt;" width="66"> <col style="width: 111pt;" width="148"> <col style="width: 92pt;" width="123"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl69" style="height: 15.75pt; width: 50pt;" width="66" height="21">Season</td> <td class="xl65" style="border-left: medium none; width: 111pt;" width="148">Number of Accounts</td> <td class="xl70" style="width: 92pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 1pt 1pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="123">Total Visit Hours</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 50pt;" width="66" height="20">Winter</td> <td class="xl66" style="border-left: medium none; width: 111pt;" width="148">*</td> <td class="xl71" style="width: 92pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 1pt 1pt 0.5pt; border-style: solid; border-color: windowtext windowtext white; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="123">*</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 50pt;" width="66" height="20">Spring</td> <td class="xl67" style="border-left: medium none; width: 111pt;" width="148">*</td> <td class="xl72" style="width: 92pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 1pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext white; background: none repeat scroll 0% 0% rgb(219, 229, 241);" width="123">*</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 50pt;" width="66" height="20">Summer</td> <td class="xl68" style="border-left: medium none; width: 111pt;" width="148">*</td> <td class="xl73" style="width: 92pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 1pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext white; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="123">*</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 50pt;" width="66" height="20">Autumn</td> <td class="xl67" style="border-left: medium none; width: 111pt;" width="148">*</td> <td class="xl72" style="width: 92pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 1pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext white; background: none repeat scroll 0% 0% rgb(219, 229, 241);" width="123">*</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 50pt;" width="66" height="20">-</td> <td class="xl68" style="border-left: medium none; width: 111pt;" width="148"> </td> <td class="xl73" style="width: 92pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 1pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext white; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="123"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 50pt;" width="66" height="20">-</td> <td class="xl67" style="border-left: medium none; width: 111pt;" width="148"> </td> <td class="xl72" style="width: 92pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 1pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext white; background: none repeat scroll 0% 0% rgb(219, 229, 241);" width="123"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 50pt;" width="66" height="20">-</td> <td class="xl68" style="border-left: medium none; width: 111pt;" width="148"> </td> <td class="xl73" style="width: 92pt; font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 1pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext white; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="123"> </td> </tr> </tbody></table>

The asterix cells need to be filled from the following data on another sheet:

<table style="border-collapse: collapse; width: 325pt;" width="434" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="123"> <col style="width: 46pt;" width="61"> <col style="width: 47pt;" width="63"> <col style="width: 101pt;" width="135"> <col style="width: 39pt;" width="52"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; width: 92pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="123" height="21">State</td> <td class="xl65" style="border-left: medium none; width: 46pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="61">Fruit</td> <td class="xl65" style="border-left: medium none; width: 47pt; border-color: -moz-use-text-color -moz-use-text-color white;" width="63">Season</td> <td class="xl65" style="border-left: medium none; width: 101pt;" width="135">Metric</td> <td valign="top">
</td><td class="xl65" style="border-left: medium none; width: 39pt;" width="52">Store</td> </tr> <tr><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 92pt;" width="123" height="20">ACT</td> <td class="xl66" style="border-left: medium none; width: 46pt;" width="61">Apple</td> <td class="xl66" style="border-left: medium none; width: 47pt;" width="63">Winter</td> <td class="xl66" style="border-left: medium none; width: 101pt;" width="135">Number of Accounts</td> <td valign="top">
</td><td class="xl66" style="border-left: medium none; width: 39pt;" width="52" align="right">52</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 92pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="123" height="20">ACT</td> <td class="xl67" style="border-left: medium none; width: 46pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="61">Banana</td> <td class="xl67" style="border-left: medium none; width: 47pt; border-color: -moz-use-text-color -moz-use-text-color white;" width="63">Spring</td> <td class="xl67" style="border-left: medium none; width: 101pt;" width="135">Total Visit hours</td> <td valign="top">
</td><td class="xl67" style="border-left: medium none; width: 39pt;" width="52" align="right">100</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 92pt;" width="123" height="20">NT</td> <td class="xl68" style="border-left: medium none; width: 46pt;" width="61">Apple</td> <td class="xl68" style="border-left: medium none; width: 47pt;" width="63">Winter</td> <td class="xl68" style="border-left: medium none; width: 101pt;" width="135">Number of Accounts</td> <td valign="top">
</td><td class="xl68" style="border-left: medium none; width: 39pt;" width="52" align="right">60</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 92pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="123" height="20">NT</td> <td class="xl67" style="border-left: medium none; width: 46pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="61">Pear</td> <td class="xl67" style="border-left: medium none; width: 47pt; border-color: -moz-use-text-color -moz-use-text-color white;" width="63">Spring</td> <td class="xl67" style="border-left: medium none; width: 101pt;" width="135">Total Visit hours</td> <td valign="top">
</td><td class="xl67" style="border-left: medium none; width: 39pt;" width="52" align="right">40</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 92pt;" width="123" height="20">QLD</td> <td class="xl68" style="border-left: medium none; width: 46pt;" width="61">Apple</td> <td class="xl68" style="border-left: medium none; width: 47pt;" width="63">Summer</td> <td class="xl68" style="border-left: medium none; width: 101pt;" width="135">Number of Accounts</td> <td valign="top">
</td><td class="xl68" style="border-left: medium none; width: 39pt;" width="52" align="right">10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 92pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="123" height="20">QLD</td> <td class="xl67" style="border-left: medium none; width: 46pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="61">Pear</td> <td class="xl67" style="border-left: medium none; width: 47pt; border-color: -moz-use-text-color -moz-use-text-color white;" width="63">Autumn</td> <td class="xl67" style="border-left: medium none; width: 101pt;" width="135">Total Visit hours</td> <td valign="top">
</td><td class="xl67" style="border-left: medium none; width: 39pt;" width="52" align="right">56</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 92pt;" width="123" height="20">VIC</td> <td class="xl68" style="border-left: medium none; width: 46pt;" width="61">Orange</td> <td class="xl68" style="border-left: medium none; width: 47pt;" width="63">Summer</td> <td class="xl68" style="border-left: medium none; width: 101pt;" width="135">Number of Accounts</td> <td valign="top">
</td><td class="xl68" style="border-left: medium none; width: 39pt;" width="52" align="right">89</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 92pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="123" height="20">VIC</td> <td class="xl67" style="border-left: medium none; width: 46pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="61">Banana</td> <td class="xl67" style="border-left: medium none; width: 47pt; border-color: -moz-use-text-color -moz-use-text-color white;" width="63">Autumn</td> <td class="xl67" style="border-left: medium none; width: 101pt;" width="135">Total Visit hours</td> <td valign="top">
</td><td class="xl67" style="border-left: medium none; width: 39pt;" width="52" align="right">40</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 92pt;" width="123" height="20">WA</td> <td class="xl68" style="border-left: medium none; width: 46pt;" width="61">Apple</td> <td class="xl68" style="border-left: medium none; width: 47pt;" width="63">Winter</td> <td class="xl68" style="border-left: medium none; width: 101pt;" width="135">Number of Accounts</td> <td valign="top">
</td><td class="xl68" style="border-left: medium none; width: 39pt;" width="52" align="right">50</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 92pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="123" height="20">WA</td> <td class="xl67" style="border-left: medium none; width: 46pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="61">Pear</td> <td class="xl67" style="border-left: medium none; width: 47pt; border-color: -moz-use-text-color -moz-use-text-color white;" width="63">Spring</td> <td class="xl67" style="border-left: medium none; width: 101pt;" width="135">Total Visit hours</td> <td valign="top">
</td><td class="xl67" style="border-left: medium none; width: 39pt;" width="52" align="right">60</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 92pt;" width="123" height="20">SA</td> <td class="xl68" style="border-left: medium none; width: 46pt;" width="61">Orange</td> <td class="xl68" style="border-left: medium none; width: 47pt;" width="63">Summer</td> <td class="xl68" style="border-left: medium none; width: 101pt;" width="135">Number of Accounts</td> <td valign="top">
</td><td class="xl68" style="border-left: medium none; width: 39pt;" width="52" align="right">47</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 92pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="123" height="20">SA</td> <td class="xl67" style="border-left: medium none; width: 46pt; border-color: -moz-use-text-color white white -moz-use-text-color;" width="61">Banana</td> <td class="xl67" style="border-left: medium none; width: 47pt; border-color: -moz-use-text-color -moz-use-text-color white;" width="63">Autumn</td> <td class="xl67" style="border-left: medium none; width: 101pt;" width="135">Total Visit hours</td> <td valign="top">
</td><td class="xl67" style="border-left: medium none; width: 39pt;" width="52" align="right">25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 92pt;" width="123" height="20"> </td> <td class="xl68" style="border-left: medium none; width: 46pt;" width="61"> </td> <td class="xl68" style="border-left: medium none; width: 47pt;" width="63"> </td> <td class="xl68" style="border-left: medium none; width: 101pt;" width="135"> </td> <td valign="top">
</td><td class="xl68" style="border-left: medium none; width: 39pt;" width="52"> </td> </tr> </tbody></table>

As the drop down is changed it needs to come from either the State, Fruit or season columns and match the relevant value from the row. It also needs to sum the data that aligns to the metric, which may appear several times in the data set.

I hope this makes sense. Once again any ideas are welcomed... I am suffering major Excel cross eyes!

Roxanne
P.S please excuse bad formatting of tables. They seem to have a mind of their own.
<table style="border-collapse: collapse;" width="78" border="0" cellpadding="0" cellspacing="0" height="20"><tbody><tr style="height: 15.75pt;" height="21"><td class="xl65" style="height: 15.75pt; width: 77pt;" width="102" height="21">
</td><td class="xl70" style="width: 48pt;" width="64">
</td><td class="xl66" style="border-left: medium none; width: 101pt;" width="135">
</td><td class="xl66" style="border-left: medium none; width: 82pt;" width="109">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td class="xl67" style="width: 48pt;" width="64">
</td><td class="xl67" style="border-left: medium none; width: 101pt;" width="135">
</td><td class="xl67" style="border-left: medium none; width: 82pt;" width="109">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td class="xl68" style="width: 48pt;" width="64">
</td><td class="xl68" style="border-left: medium none; width: 101pt;" width="135">
</td><td class="xl68" style="border-left: medium none; width: 82pt;" width="109">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td class="xl69" style="width: 48pt;" width="64">
</td><td class="xl69" style="border-left: medium none; width: 101pt;" width="135">
</td><td class="xl69" style="border-left: medium none; width: 82pt;" width="109">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td class="xl68" style="width: 48pt;" width="64">
</td><td class="xl68" style="border-left: medium none; width: 101pt;" width="135">
</td><td class="xl68" style="border-left: medium none; width: 82pt;" width="109">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td class="xl69" style="width: 48pt;" width="64">
</td><td class="xl69" style="border-left: medium none; width: 101pt;" width="135">
</td><td class="xl69" style="border-left: medium none; width: 82pt;" width="109">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td class="xl68" style="width: 48pt;" width="64">
</td><td class="xl68" style="border-left: medium none; width: 101pt;" width="135">
</td><td class="xl68" style="border-left: medium none; width: 82pt;" width="109">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td class="xl69" style="width: 48pt;" width="64">
</td><td class="xl69" style="border-left: medium none; width: 101pt;" width="135">
</td><td class="xl69" style="border-left: medium none; width: 82pt;" width="109">
</td> </tr> </tbody></table>

 

[AlphaFrog]

The "Data" table has only one column of numbers under the heading "Store".

I don't follow what you want as results (asterisks) under "Number of Accounts" and "Total Visit Hours"

You say; "I know that a pivot table will easily do this, however I need to do further analysis to the data that won't work with a pivot." Perhaps there is a way to get the pivot to do what you want. I'm a big fan of the pivot. Can you explain what you mean by "further analysis"?