Pivot Table result isn't displaying all the data that it should

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kim - 02 Oct 2007 22:29 GMT

I have a pivot table that I use to list invoices paid on a certain date. I
fill in this field in the spreadsheet, refresh the pivot table and select the
date I want from the dropdown list but it doesn't always list all the
invoices. Then I'll refresh it again and it displays an extra invoice or two
but not all the ones it should. I've verified that I entered all the info
correctly and try to refresh multiple times but it doesn't display all the
data. If I delete the pivot table and recreate it then it finds all the
invoices. Later, it happens again. Has this happened to anyone?

Reply to this Message

Roger Govier - 02 Oct 2007 22:44 GMT

Hi Kim

I suspect that when you create the PT, you are giving it a fixed source data
range.
As your data continues beyond this range, then the PT cannot "see it".

Instead, create a Dynamic range, which will grow automatically as you add
more data.
Insert>Name>Define> Name MyData Refers to
=(OFFSET($A$1,0,0,COUNTA($A$A),COUNTA($1:$1))

Note this assumes that column A will always be populated with data. If it
will not, then choose a column letter where you know all rows will have data
entered. Similarly, the formula assumes that your headers are in row 1, and
that you have nothing in row beyond your table data.

Back on your PT, right click>Pivot Table Wizard>Back> enter =MyData in the
range box>Finish

If you have any problems, post back.

Signature

Regards
Roger Govier

>I have a pivot table that I use to list invoices paid on a certain date. I
> fill in this field in the spreadsheet, refresh the pivot table and select
[quoted text clipped - 6 lines]
> data. If I delete the pivot table and recreate it then it finds all the
> invoices. Later, it happens again. Has this happened to anyone?

Reply to this Message