unexpected data in pivot table

[merlin777]

i have a range which contains company names in the rows and various financial data in the columns. Lines are often added and removed.

I put a pivot table on another sheet for this range. What is strange is that some company names are ib the pivot table that are not in the original range any more.

They used to be in the range but i removed them. I do that by copying and pasting them to a separate range at the bottom of the sheet and then deleting the row (i don't cut and paste because i have some vba which runs every minute to get share prices from an external website and if i'm in the middle of a cut and paste the data disappears!)

I specified the range for the pivot table with a name - 'table1'. I'm not sure if it's a list, table or what but it shows up in the drop down for cell references top left and when i click on it the range is exactly selected. there are no other cells selected that i can see.

How can the pivot table list data that's not been in the range since before the pt was created?

 

[merlin777]

btw - i checked for hidden rows.

 

[RoryA]

If your pivot table is set to remember old items and you have the field set to show items with no data, then you could see non-existent items in the actual pivot table (but the values should all be blank).

 

[merlin777]

these rows were deleted before the pt was created and they have a full set of data in the rest of the row.

 

[Oeldere]

@merlin777


You need to refresh the pivot table, after editing the full set of data.

Excel 2007 => data => refresh (pivot table)

 

[merlin777]

that did the job - thanks.

what i don't understand is why i had to refresh a pivot table i created a few hours ago to get rid of data that hasn't been in the range for months?

where did it get it from in the first place!

 

[RoryA]

Are there any other pivot tables in the workbook?

 

[merlin777]

there is an old one. i cant see these companies in it though (but then i guess i refreshed them all?

 

[RoryA]

If they used the same data, they would be sharing a pivotcache so the new pivot would just use the cache already there, and that already had the missing data in it. You can tell the tables not to retain missing items in the pivot table properties dialog.

 

[merlin777]

smashing - thanks, Rory!