Structured reference problem using table

[sccole]

I am using the table function and structured reference for a formula on the far right marked in yellow, "YTD Quota". That formula is: =SUM(Main[@[Jan]:[Mar]]), and sums Jan, Feb and Mar (also in yellow) in the row "Name 1". I would like the second sum term in the formula "Mar" to be variable and to refer to a value outside the table. This way I could change Mar to Apr, May etc in the "Month to measure field" at the top of the table. Is there any way to do this? - thanks


<table border="0" cellpadding="0" cellspacing="0" width="1064"><colgroup><col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2633; width:54pt" span="10" width="72"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;width:82pt" height="17" width="109">Month to measure</td> <td class="xl70" style="width:59pt" width="79"> Mar </td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:54pt" width="72">
</td> <td class="xl65" style="width:55pt" width="73">
</td> <td class="xl65" style="width:62pt" width="83">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</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> <td class="xl65">
</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:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none" height="17">Name</td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Jan </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Feb </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Mar </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Apr </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> May </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Jun </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Jul </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Aug </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Sep </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Oct </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Nov </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> Dec </td> <td class="xl68" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none"> YTD Quota </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none" height="17">Name 1</td> <td class="xl71" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:yellow; mso-pattern:black none"> 231,000 </td> <td class="xl71" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:yellow; mso-pattern:black none"> 254,100 </td> <td class="xl71" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:yellow; mso-pattern:black none"> 284,900 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 252,000 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 277,200 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 310,800 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 273,000 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 300,300 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 336,700 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 294,000 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 323,400 </td> <td class="xl66" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:#D9D9D9; mso-pattern:#D9D9D9 none"> 362,600 </td> <td class="xl71" style="border-left:none;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid windowtext;background:yellow; mso-pattern:black none"> 770,000 </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none;font-size: 10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border:.5pt solid windowtext" height="17">Name 2</td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 231,000 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 254,100 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 284,900 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 252,000 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 277,200 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 310,800 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 273,000 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 300,300 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 336,700 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 294,000 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 323,400 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 362,600 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext"> 770,000 </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none;font-size: 10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none" height="17">Name 3</td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 462,000 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 508,200 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 569,800 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 504,000 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 554,400 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 621,600 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 546,000 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 600,600 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 673,400 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 588,000 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 646,800 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 725,200 </td> <td class="xl66" style="border-top:none;border-left:none;font-size:10.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Arial;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none"> 1,540,000 </td> </tr> </tbody></table>

 

[Excelestial]

Create a drop-down to enable you to select a month (or type in the month you want to change)....slave the formula to reference the drop-down or cell with the changing month.

 

[sccole]

Thanks for the note, but it won't let me reference the cell. I put in "=B1" in place of "Mar" in the formula and I come up with an error. I've tried lots of permutations but nothing seems to work.