Empty values in Pivot table?

LostInDaJungle

New Member
Joined
Feb 27, 2012
Messages
47
I have a series of accounts and period balances coming from a database.

Each line is basically:
Year - Month - Account - Netchange

I'm using Pivot tables to summarize this information for YTD calcs.

I put "NETCHANGE" into the values of the PivotTable and set the name to YTD and the Set the type to running total by month.

I can then call an individual account like so:

=GETPIVOTDATA("YTD",ptAll,"FISCALYEAR",2011,"FISCALPERIOD",12,"ACCOUNTNO","10000000")

It works like a dream except for those odd accounts that don't get much activity. Some accounts did not have a change in December of the target year, so there is no PivotTable line for 12/2011.

I've played with all of the PivotTable options that would seem to "fill in blanks" and none work for me. How can I get my PivotTable to treat empty months as a 0 netchange and give me a total for December?

Here's what my pivot table looks like:
Budgeting+Model+Package.xlsx+872012+30113+PM.jpg


Thanks in advance for your help.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,724
Members
414,013
Latest member
tnobbs

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