Consolidation and summary... how to proceed?

[synergy46]

I have two main tables: ACTUAL and BUDGET

The Actual Table collects information as checks are writen and distributes the amounts to the proper column. It works ok.

I also have a Budget Table which collects information with a single budget amount per Account. See below.

(The structure is not locked in stone)

THE PROBLEM:
I have tried PivotTables but even in "Multiple Consolidated Ranges" I get garbage. I need to have a single table that shows both the Summary Information by Actual . (The PT works great for the Actual Summary)

But, I need a table that consolidates the Actual vs Budgeted by account, year and month.

I have XL 2008 and XL 2004 but I would prefer not having to crank VBA to do this; but I can if needed. I have a suspicion that there is some kind of lookup function that would allow me to lookup the Budget Account in the Actual Table and then pull up the Sum of that Account. But, it gets hazzy for me when I try and factor in the year and month.

Your thoughts are appreciated.

Thanks

PS Yes, I bought Mr. Excel's "Pivot Table Data Crunching" but it doesn't address this issue.

 

[Sandeep Warrier]

Hi

The data you posted is cannot be seen. Can you repost using Excel Jeanie or RichardScholar's HTML Maker ?
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[synergy46]

I have two main tables: ACTUAL and BUDGET

The Actual Table collects information as checks are writen and distributes the amounts to the proper column. It works ok.

I also have a Budget Table which collects information with a single budget amount per Account. See below.

(The structure is not locked in stone)

THE PROBLEM:
I have tried PivotTables but even in "Multiple Consolidated Ranges" I get garbage. I need to have a single table that shows both the Summary Information by Actual . (The PT works great for the Actual Summary)

But, I need a table that consolidates the Actual vs Budgeted by account, year and month.

I have XL 2008 and XL 2004 but I would prefer not having to crank VBA to do this; but I can if needed. I have a suspicion that there is some kind of lookup function that would allow me to lookup the Budget Account in the Actual Table and then pull up the Sum of that Account. But, it gets hazzy for me when I try and factor in the year and month.

Your thoughts are appreciated.

Thanks

PS Yes, I bought Mr. Excel's "Pivot Table Data Crunching" but it doesn't address this issue.

I resubmitted the pix of my spreadsheets and hope they show for you.

 

[synergy46]

I resubmitted the pix of my spreadsheets and hope they show for you.

Try this link: http://sites.google.com/site/tester46site/budget3

 

[Sandeep Warrier]

Hi,

The advantage in posting your data using the HTML makers is that we can copy-paste it in Excel instead of typing out the entire setup.

If you have installation problems then ask Richard for his HTML maker (link to it in my last post). It does not require any installation.

 

[synergy46]

Hi,

The advantage in posting your data using the HTML makers is that we can copy-paste it in Excel instead of typing out the entire setup.

If you have installation problems then ask Richard for his HTML maker (link to it in my last post). It does not require any installation.

========
Thanks for the reply. Since I am not looking to buy a utility to provide visibility of my tables, I need to ask "Is there some way to upload to this forum my spreadsheet.?" I don't see anything except the link button...?

 

[VoG]

========
Thanks for the reply. Since I am not looking to buy a utility to provide visibility of my tables, I need to ask "Is there some way to upload to this forum my spreadsheet.?" I don't see anything except the link button...?

Both of the HTML makers that Sandeep posted links to are free, gratis and for nothing

 

[synergy46]

Both of the HTML makers that Sandeep posted links to are free, gratis and for nothing

Thanks for the 'clarification' but I am running XL2008 for Mac and the 1st program appears to require Windows and the 2nd doesn't seem to work with a Mac.... Thanks for the suggestion.

 

[VoG]

Thanks for the 'clarification' but I am running XL2008 for Mac and the 1st program appears to require Windows and the 2nd doesn't seem to work with a Mac.... Thanks for the suggestion.

Try just copying data from your sheet and pasting it here.

 

[synergy46]

Try just copying data from your sheet and pasting it here.

OK. Her goes... Below is the Actual Table.

Version:1.0 StartHTML:0000000197 EndHTML:0000010995 StartFragment:0000001776 EndFragment:0000010949 SourceURL:file://localhost/Users/Ron/Documents/MS%20Office/MS%20Excel/Budget%20/budget.xls ****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 11"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/Ron/Library/Caches/TemporaryItems/msoclip1/01/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {mso-number-format:"mmm\\ dd\\ yyyy";} .xl25 {mso-number-format:Standard;} .xl26 {mso-number-format:"_\(* \#\,\#\#0\.00_\)\;_\(* \\\(\#\,\#\#0\.00\\\)\;_\(* \0022-\0022??_\)\;_\(\@_\)";} .xl27 {mso-number-format:0;} .xl28 {font-weight:700;} .xl29 {color:#DD0806; mso-number-format:Standard;} .xl30 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; text-align:right;} --> </style> <table style="border-collapse: collapse; width: 665px; height: 388px;" border="0" cellpadding="0" cellspacing="0"> <!--StartFragment--> <col style="" span="2" width="77"> <col style="" width="79"> <col style="" width="74"> <col style="" width="79"> <col style="" width="80"> <col style="" span="2" width="64"> <col style="" width="71"> <tbody><tr height="13"> <td width="77" height="13"></td> <td width="77"></td> <td width="79"></td> <td colspan="2" width="153">INPUT LIST TABLE</td> <td width="80"></td> <td width="64"></td> <td width="64"></td> <td width="71"></td> </tr> <tr height="13"> <td height="13"></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct</td> <td>Date</td> <td>Check No.</td> <td class="xl29">Amt</td> <td>Jan Actual</td> <td>Feb Actual</td> <td>Mar Actual</td> <td>Apr Actual</td> <td>May Actual</td> </tr> <tr height="13"> <td height="13">acct1</td> <td class="xl24" x:num="38718.0" align="right">Jan 02 2010</td> <td class="xl27" x:num="123.0" align="right">123</td> <td class="xl30" x:num="1000.0"> $1,000.00 </td> <td class="xl25" x:num="1000.0" align="right">1,000.00</td> <td class="xl26" align="right"> </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct3</td> <td class="xl24" x:num="38718.0" align="right">Jan 02 2010</td> <td class="xl27" x:num="124.0" align="right">124</td> <td class="xl30" x:num="200.0"> $200.00 </td> <td class="xl25" x:num="200.0" align="right">200.00</td> <td class="xl26" align="right"> </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct2</td> <td class="xl24" x:num="38749.0" align="right">Feb 02 2010</td> <td class="xl27">debit card</td> <td class="xl30" x:num="60.0"> $60.00 </td> <td class="xl25"></td> <td class="xl26" x:num="60.0" align="right"> 60.00 </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct3</td> <td class="xl24" x:num="38750.0" align="right">Feb 03 2010</td> <td class="xl27" x:num="125.0" align="right">125</td> <td class="xl30" x:num="1000.0"> $1,000.00 </td> <td class="xl25"></td> <td class="xl26" x:num="1000.0" align="right"> 1,000.00 </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct2</td> <td class="xl24" x:num="38750.0" align="right">Feb 03 2010</td> <td class="xl27" x:num="126.0" align="right">126</td> <td class="xl30" x:num="3000.0"> $3,000.00 </td> <td class="xl25"></td> <td class="xl26" x:num="3000.0" align="right"> 3,000.00 </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct3</td> <td class="xl24" x:num="38776.0" align="right">Mar 01 2010</td> <td class="xl27" x:num="127.0" align="right">127</td> <td class="xl30" x:num="50.0"> $50.00 </td> <td class="xl25"></td> <td class="xl26" align="right"> </td> <td x:num="50.0" align="right">50</td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct2</td> <td class="xl24" x:num="38810.0" align="right">Apr 04 2010</td> <td class="xl27" x:num="128.0" align="right">128</td> <td class="xl30" x:num="788.0"> $788.00 </td> <td class="xl25"></td> <td class="xl26" align="right"> </td> <td></td> <td x:num="788.0" align="right">788</td> <td></td> </tr> <tr height="13"> <td height="13">acct1</td> <td class="xl24" x:num="38721.0" align="right">Jan 05 2010</td> <td class="xl27" x:num="129.0" align="right">129</td> <td class="xl30" x:num="78.0"> $78.00 </td> <td class="xl25" x:num="78.0" align="right">78.00</td> <td class="xl26" align="right"> </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct3</td> <td class="xl24" x:num="38812.0" align="right">Apr 06 2010</td> <td class="xl27" x:num="130.0" align="right">130</td> <td class="xl30" x:num="888.0"> $888.00 </td> <td class="xl25"></td> <td class="xl26" align="right"> </td> <td></td> <td x:num="888.0" align="right">888</td> <td></td> </tr> <tr height="13"> <td height="13">acct1</td> <td class="xl24" x:num="38828.0" align="right">Apr 22 2010</td> <td class="xl27" x:num="1131.0" align="right">1131</td> <td class="xl30" x:num="999.0"> $999.00 </td> <td class="xl25"></td> <td class="xl26" align="right"> </td> <td></td> <td x:num="999.0" align="right">999</td> <td></td> </tr> <tr height="13"> <td height="13">acct2</td> <td class="xl24" x:num="38830.0" align="right">Apr 24 2010</td> <td class="xl27" x:num="1131.0" align="right">1131</td> <td class="xl30" x:num="8888.0"> $8,888.00 </td> <td class="xl25"></td> <td class="xl26" align="right"> </td> <td></td> <td x:num="8888.0" align="right">8888</td> <td></td> </tr> <tr height="13"> <td height="13">acct1</td> <td class="xl24" x:num="38732.49831018518" align="right">Jan 16 2010</td> <td class="xl27" x:num="1133.0" align="right">1133</td> <td class="xl30" x:num="300.0"> $300.00 </td> <td class="xl25" x:num="300.0" align="right">300.00</td> <td class="xl26" align="right"> </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct2</td> <td class="xl24" x:num="38732.49831018518" align="right">Jan 16 2010</td> <td class="xl27"></td> <td class="xl30" x:num="434.0"> $434.00 </td> <td class="xl25" x:num="434.0" align="right">434.00</td> <td class="xl26" align="right"> </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct1</td> <td class="xl24" x:num="38732.49831018518" align="right">Jan 16 2010</td> <td class="xl27"></td> <td class="xl30" x:num="688.0"> $688.00 </td> <td class="xl25" x:num="688.0" align="right">688.00</td> <td class="xl26" align="right"> </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">acct2</td> <td class="xl24" x:num="38732.49831018518" align="right">Jan 16 2010</td> <td class="xl27"></td> <td class="xl30" x:num="55.0"> $55.00 </td> <td class="xl25" x:num="55.0" align="right">55.00</td> <td class="xl26" align="right"> </td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13"></td> <td class="xl24" x:num="38778.0" align="right">Mar 03 2010</td> <td class="xl27"></td> <td class="xl30" x:num="33.0"> $33.00 </td> <td class="xl25"></td> <td class="xl26" align="right"> </td> <td x:num="33.0" align="right">33</td> <td></td> <td></td> </tr> <tr height="13"> <td height="13"></td> <td class="xl24" x:num="38778.0" align="right">Mar 03 2010</td> <td class="xl27"></td> <td class="xl30" x:num="44.0"> $44.00 </td> <td class="xl25"></td> <td class="xl26" align="right"> </td> <td x:num="44.0" align="right">44</td> <td></td> <td></td> </tr> <tr height="13"> <td height="13"></td> <td class="xl24" x:num="38810.0" align="right">Apr 04 2010</td> <td class="xl27"></td> <td class="xl30" x:num="55.0"> $55.00 </td> <td class="xl25"></td> <td class="xl26" align="right"> </td> <td></td> <td x:num="55.0" align="right">55</td> <td></td> </tr> <tr height="13"> <td class="xl28" height="13"></td> <td class="xl28"></td> <td class="xl28"></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td class="xl28"></td> </tr> <tr height="13"> <td height="13"></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13"></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13"></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <!--EndFragment--> </tbody></table>
And here is the budget table:
Version:1.0 StartHTML:0000000197 EndHTML:0000003287 StartFragment:0000001366 EndFragment:0000003241 SourceURL:file://localhost/Users/Ron/Documents/MS%20Office/MS%20Excel/Budget%20/budget.xls ****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 11"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/Ron/Library/Caches/TemporaryItems/msoclip1/01/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {white-space:nowrap; mso-text-control:shrinktofit;} --> </style> <table style="border-collapse: collapse;" width="481" border="0" cellpadding="0" cellspacing="0"> <!--StartFragment--> <col style="" width="100"> <col style="" width="95"> <col style="" width="49"> <col style="" width="81"> <col style="" span="2" width="78"> <tbody><tr height="13"> <td width="100" height="13"></td> <td width="95"></td> <td class="xl24" width="49"></td> <td width="81"></td> <td width="78"></td> <td width="78"></td> </tr> <tr height="13"> <td height="13">Acct</td> <td class="xl24">Year</td> <td>Jan Budget</td> <td>Feb Budget</td> <td>Mar Budget</td> <td>Apr Budget</td> </tr> <tr height="13"> <td height="13">acct1</td> <td class="xl24" x:num="2010.0" align="right">2010</td> <td x:num="111.0" align="right">111</td> <td x:num="211.0" align="right">211</td> <td x:num="311.0" align="right">311</td> <td x:num="411.0" align="right">411</td> </tr> <tr height="13"> <td height="13">acct2</td> <td class="xl24" x:num="2011.0" align="right">2011</td> <td x:num="112.0" align="right">112</td> <td x:num="212.0" align="right">212</td> <td x:num="312.0" align="right">312</td> <td x:num="412.0" align="right">412</td> </tr> <tr height="13"> <td height="13">acct3</td> <td class="xl24" x:num="2012.0" align="right">2012</td> <td x:num="113.0" align="right">113</td> <td x:num="213.0" align="right">213</td> <td x:num="313.0" align="right">313</td> <td x:num="413.0" align="right">413</td> </tr> <tr height="13"> <td height="13"></td> <td class="xl24"></td> <td class="xl24"></td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13"></td> <td></td> <td class="xl24"></td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13"></td> <td></td> <td class="xl24"></td> <td></td> <td></td> <td></td> </tr> <!--EndFragment--> </tbody></table> And here is the PivotTable that is constructed from the ACTUALS:
Version:1.0 StartHTML:0000000197 EndHTML:0000006880 StartFragment:0000003131 EndFragment:0000006834 SourceURL:file://localhost/Users/Ron/Documents/MS%20Office/MS%20Excel/Budget%20/budget.xls ****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 11"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/Ron/Library/Caches/TemporaryItems/msoclip1/01/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {border:.5pt solid black;} .xl25 {border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl26 {border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:none;} .xl27 {border-top:.5pt solid black; border-right:none; border-bottom:.5pt solid black; border-left:.5pt solid black;} .xl28 {border-top:none; mso-border-top-source:background; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl29 {border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl30 {border-top:.5pt solid black; border-right:none; border-bottom:.5pt solid black; border-left:none; mso-border-left-source:background;} .xl31 {border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:none; mso-border-left-source:background;} .xl32 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; border-top:.5pt solid black; border-right:.5pt solid black; border-bottom:none; border-left:.5pt solid black;} .xl33 {border-top:.5pt solid black; border-right:.5pt solid black; border-bottom:none; border-left:.5pt solid black;} .xl34 {mso-number-format:"mmm\\ dd\\ yyyy"; border-top:.5pt solid black; border-right:none; border-bottom:none; border-left:.5pt solid black;} .xl35 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; border-top:none; border-right:.5pt solid black; border-bottom:none; border-left:.5pt solid black;} .xl36 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; border:.5pt solid black;} --> </style> <table style="border-collapse: collapse;" width="325" border="0" cellpadding="0" cellspacing="0"> <!--StartFragment--> <col style="" width="100"> <col style="" width="95"> <col style="" width="49"> <col style="" width="81"> <tbody><tr height="13"> <td width="100" height="13"></td> <td width="95"></td> <td width="49"></td> <td width="81"></td> </tr> <tr height="13"> <td height="13"></td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td class="xl25" height="13">Sum of Amt</td> <td class="xl26"> </td> <td class="xl26"> </td> <td class="xl33"> </td> </tr> <tr height="13"> <td class="xl24" height="13">Year</td> <td class="xl24">Month</td> <td class="xl24">Account</td> <td class="xl33">Total</td> </tr> <tr height="13"> <td class="xl25" height="13">2010</td> <td class="xl34">Jan</td> <td class="xl25">acct1</td> <td class="xl32" x:num="1078.0" align="right"> $1,078.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl28"> </td> <td class="xl29">acct3</td> <td class="xl35" x:num="200.0" align="right"> $200.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl34">Jan Total</td> <td class="xl31"> </td> <td class="xl32" x:num="1278.0" align="right"> $1,278.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl34">Feb</td> <td class="xl25">acct2</td> <td class="xl32" x:num="3060.0" align="right"> $3,060.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl28"> </td> <td class="xl29">acct3</td> <td class="xl35" x:num="1000.0" align="right"> $1,000.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl34">Feb Total</td> <td class="xl31"> </td> <td class="xl32" x:num="4060.0" align="right"> $4,060.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl34">Mar</td> <td class="xl25">acct3</td> <td class="xl32" x:num="50.0" align="right"> $50.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl34">Mar Total</td> <td class="xl31"> </td> <td class="xl32" x:num="50.0" align="right"> $50.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl34">Apr</td> <td class="xl25">acct1</td> <td class="xl32" x:num="999.0" align="right"> $999.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl28"> </td> <td class="xl29">acct2</td> <td class="xl35" x:num="9676.0" align="right"> $9,676.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl28"> </td> <td class="xl29">acct3</td> <td class="xl35" x:num="888.0" align="right"> $888.00 </td> </tr> <tr height="13"> <td class="xl28" height="13"> </td> <td class="xl34">Apr Total</td> <td class="xl31"> </td> <td class="xl32" x:num="11563.0" align="right"> $11,563.00 </td> </tr> <tr height="13"> <td class="xl27" height="13">Grand Total</td> <td class="xl30"> </td> <td class="xl30"> </td> <td class="xl36" x:num="16951.0" align="right"> $16,951.00 </td> </tr> <!--EndFragment--> </tbody></table>
Great idea about 'pasting'. It is so obvious I didn't think of it!