Any ideas on how I can do this?

[bcurrey]

I have three tabs in a spreadsheet with pivot tabs in each one showing data in the format below. The first tab shows Month to Date numbers, the next shows prior month numbers, and the last one shows year to date numbers.


<table border="0" cellpadding="0" cellspacing="0" width="307"><colgroup><col style="width:54pt" span="3" width="72"> <col style="mso-width-source:userset;mso-width-alt:2912;width:68pt" width="91"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:54pt" height="20" width="72">Rep</td> <td class="xl63" style="width:54pt" width="72">Sales</td> <td class="xl63" style="width:54pt" width="72">Budget</td> <td class="xl63" style="width:68pt" width="91">% to plan</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">Sale 1</td> <td class="xl65" align="right">$10,000</td> <td class="xl65" align="right">$7,500</td> <td class="xl64" align="right">133.33%</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">Sale 2</td> <td class="xl65" align="right">$5,000</td> <td class="xl65" align="right">$7,500</td> <td class="xl64" align="right">66.67%</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">Sale 3</td> <td class="xl65" align="right">$7,500</td> <td class="xl65" align="right">$7,500</td> <td class="xl64" align="right">100.00%</td> </tr> </tbody></table>

I would like to combine grab the % to plan within each pivot table and past on a summary tab like this:

<table border="0" cellpadding="0" cellspacing="0" width="347"><colgroup><col style="width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:3072;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:2816;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:2912;width:68pt" width="91"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:54pt" height="20" width="72">
</td> <td class="xl65" style="width:72pt" width="96"> MTD</td> <td class="xl65" style="width:66pt" width="88"> Prior</td> <td class="xl65" style="width:68pt" width="91"> YTD</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Rep</td> <td class="xl65">% to plan</td> <td class="xl65">% to plan</td> <td class="xl65">% to plan</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl67" style="height:14.25pt" height="19">Sale 1</td> <td class="xl66" align="right">140.00%</td> <td class="xl66" align="right">90.00%</td> <td class="xl66" align="right">133.33%</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl67" style="height:14.25pt" height="19">Sale 2</td> <td class="xl66" align="right">40.00%</td> <td class="xl66" align="right">85.00%</td> <td class="xl66" align="right">66.67%</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl67" style="height:14.25pt" height="19">Sale 3</td> <td class="xl66" align="right">110.00%</td> <td class="xl66" align="right">80.00%</td> <td class="xl66" align="right">100.00%</td> </tr> </tbody></table>


Any ideas on how/if I can do this? Thanks!!

 

[GlennUK]

Have a look at the GETPIVOTDATA function.