A pivot table gets refreshed weekly and this pivot table is a source for
other calculations that rely on the presence of a column of data for each
month. Currently, if the source data does not contain value for April, for
example, then the column for April does not exist in the pivot table. This
scews up the relative reference used in the calculation of summary data. If
there is nor data for April, then the number 0 should appear.

Can anyone help?

First, let's say you have a field in your data labeled "Month". In order for
all the months to appear in your pivot table, they must be listed in "Month",
even if there is no data associated with a particular month. So if you don't
have "April" in the month column at all, it will never show up in the pivot
table.

Now, let's say all the months are listed under "Month", but some of their
associated data entries are blank (for example, "April" is listed, but its
data is blank). If you double-click on the name of the field in your pivot
table (in this case, the "Month" field), a dialog box comes up. There is a
check box that says "Show items with no data". Make sure that box is
checked. Then every month will show up in the pivot table.

The cell under that month might be blank, however. If you want it to show
zero (0) for a blank cell, select "Table Options..." from the pivot table
tool bar, and put a zero in the box for "For empty cells, show:" and make
sure the checkbox is checked.

HTH,

Eric


"bendinblues" wrote:

A pivot table gets refreshed weekly and this pivot table is a source for
other calculations that rely on the presence of a column of data for each
month. Currently, if the source data does not contain value for April, for
example, then the column for April does not exist in the pivot table. This
scews up the relative reference used in the calculation of summary data. If
there is nor data for April, then the number 0 should appear.

Can anyone help?

Take a look at the getpivotdata formula...

http://www.contextures.com/xlPivot06.html
--
HTH...

Jim Thomlinson


"EricG" wrote:

First, let's say you have a field in your data labeled "Month". In order for
all the months to appear in your pivot table, they must be listed in "Month",
even if there is no data associated with a particular month. So if you don't
have "April" in the month column at all, it will never show up in the pivot
table.

Now, let's say all the months are listed under "Month", but some of their
associated data entries are blank (for example, "April" is listed, but its
data is blank). If you double-click on the name of the field in your pivot
table (in this case, the "Month" field), a dialog box comes up. There is a
check box that says "Show items with no data". Make sure that box is
checked. Then every month will show up in the pivot table.

The cell under that month might be blank, however. If you want it to show
zero (0) for a blank cell, select "Table Options..." from the pivot table
tool bar, and put a zero in the box for "For empty cells, show:" and make
sure the checkbox is checked.

HTH,

Eric


"bendinblues" wrote:

A pivot table gets refreshed weekly and this pivot table is a source for
other calculations that rely on the presence of a column of data for each
month. Currently, if the source data does not contain value for April, for
example, then the column for April does not exist in the pivot table. This
scews up the relative reference used in the calculation of summary data. If
there is nor data for April, then the number 0 should appear.

Can anyone help?