Updating a table & refresh the pivot I lose data.

[Sha321]

Hi all,

I have monthly data for sales reps. I started with January data. Built my pivot table & then had to 'pivot the pivot' to get the counts I needed. When I add in February data strange things happened. I have all the detail within the detail table. The first pivot is refreshed & I see the February data. I refresh on the Pivot-of-pivot table and my slicers lose some values & others are shaded out.

HELP please!

[XL&ME]

Not sure I fully understand your problem. If there is no data in the columns AK to XFD, suggest you delete them. This unused space is taking up a lot of memory. Where are the names coming from for the new tank count pivot? The "greyed" out slicers are ones that do not show any data from what I can tell on your Data YTD worksheet. For example, I did not see David Peck in the salesperson column.

[dflak]

A couple of things:

1. As XL&ME suggested, ditch the extra columns.

2. Your data for the Salesperson Pivot is in an Excel table. Instead of using whole columns, use Table1 as the data source. This way you only work with the exact amount of data you need.

3. The salesperson pivot table is subject to growth. Unfortunately, you can't overlay an Excel Table over a pivot table. However, you can overlay a named dynamic range and use it as the data source. I created a name called Tank_Count_Source =OFFSET('Salesperson Pivot'!$A$5,0,0,COUNTA('Salesperson Pivot'!$A:$A)-3,7). The reason for the -3 is we don't want to count the filters or the summary line.

For more details on dynamic ranges, see this article: http://www.utteraccess.com/wiki/index.php/Pivot_Tables.

[Sha321]

Thanks XL&ME. The reason you don't see David Peck is because I sterilized the detail data. (You know, hide the bodies, etc.) The shaded tabs are what is left & are part of the problem. The data that belongs to David will be "Sales DP".

I appreciate your response.

[Sha321]

Thanks Dflak. I'll certainly try your suggestions. Trying to use more robust features & usually getting into trouble. But WTH! I'll let you know how I make out.

I appreciate your response.