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

Roxexcel

New Member
Joined
Jul 8, 2010
Messages
6
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
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

New Member
Joined
Jul 8, 2010
Messages
6
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

New Member
Joined
Jul 8, 2010
Messages
6
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
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"?
 

Forum statistics

Threads
1,136,434
Messages
5,675,836
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top