Help wiht pivot table - excel 2010

[ybenner]

I need help with creating the correct formula for a pivot table. I'm pulling spending data from an Access file and importing into excel. The data looks like the example below. It has the project number - total budget for the project and invoices booked against it. So per the data, Project 1111 has a budget of $1000 and had two $500 invoices booked against it which means that there are $0 dollars left remaining to spend. (obviously )

                                                                           <table style="border-collapse: collapse; width: 407pt;" border="0" cellpadding="0" cellspacing="0" width="543"><col style="width: 80pt;" width="107"> <col style="width: 53pt;" width="71"> <col style="width: 47pt;" width="63"> <col style="width: 81pt;" span="2" width="108"> <col style="width: 65pt;" width="86"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 80pt;" height="20" width="107">Project   Number</td> <td class="xl65" style="border-left: medium none; width: 53pt;" width="71">Budget</td> <td class="xl65" style="border-left: medium none; width: 47pt;" width="63">Vendor</td> <td class="xl65" style="border-left: medium none; width: 81pt;" width="108">Invoice Amount</td> <td class="xl65" style="border-left: medium none; width: 81pt;" width="108">Invoice Number</td> <td class="xl65" style="border-left: medium none; width: 65pt;" width="86">Invoice  Date</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 80pt;" align="right" height="20" width="107">1111</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="71">$1,000.00</td> <td class="xl66" style="border-left: medium none; width: 47pt;" width="63">vendor1</td> <td class="xl67" style="border-left: medium none; width: 81pt;" width="108">$500.00</td> <td class="xl66" style="border-left: medium none; width: 81pt;" align="right" width="108">1 
</td> <td class="xl68" style="border-left: medium none; width: 65pt;" width="86">01-Jan-11</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none; width: 80pt;" align="right" height="20" width="107">1111</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">$1,000.00</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">vendor1</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 81pt;" width="108">$500.00</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 81pt;" align="right" width="108">2</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 65pt;" width="86">01-Jan-11</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none; width: 80pt;" align="right" height="20" width="107">2222</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">$3,000.00</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">vendor2</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 81pt;" width="108">$1,000.00</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 81pt;" align="right" width="108">1</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 65pt;" width="86">22-Feb-11</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none; width: 80pt;" align="right" height="20" width="107">2222</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">$3,000.00</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">vendor2</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 81pt;" width="108">$1,000.00</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 81pt;" align="right" width="108">1</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 65pt;" width="86">22-Feb-11</td><td valign="top">
</td><td valign="top">
</td> </tr> </tbody></table>

My problem is that I need to be able to show the "Dollars Remaining" on the pivot table but I can't get it calculated right. I can create sum of the "invoice amount" and group that by month in coulmns, but when I try to create a calculated field using "budget" - "amount," it adds up the rows for each project making it appear that project 1111 has a total budget of $2000 (since there are two rows of invoices for this project) even though the true budget is $1000. Hope I didn't confuse you!

Do I need to remove this budget column? If I do, How do I add the budget amount to the pivot table to make the "dollars remaining" calculation?

If I need to use the "GETPIVOTDATA" function, how can I integrate the data into the pivot table report so that it can group it together by project nice and pretty?

halp

 

[GlennUK]

Shouldn't the layout be more like:

Excel Workbook
	A	B	C	D	E	F
1	Project Number	Entry type	Vendor	Amount	Invoice Number	Invoice Date
2	1111	Invoice	vendor1	500.00	1	01-Jan-11
3	1111	Invoice	vendor1	500.00	2	01-Jan-11
4	1111	Budget		1,000.00	0
5	2222	Invoice	vendor2	1,000.00	1	22-Feb-11
6	2222	Invoice	vendor2	1,000.00	2	22-Feb-11
7	2222	Budget		3,000.00	0
Sheet9

Then you can have the pivottable do a calculated item for the difference. It might look like this:

Excel Workbook
	A	B	C	D	E
8
9	Sum of Amount	Entry type
10	Project Number	Budget	Invoice	Remaining	Grand Total
11	1111	1000	1000	0	2000
12	2222	3000	2000	1000	6000
13	Grand Total	4000	3000	1000	8000
14
15
Sheet9

 

[ybenner]

Re: Help with pivot table - excel 2010

I'll see if that helps. Thanks.