Only one entry per Month in Pivot Table

[rushdenx1]

I have created a Pivot Table in the enclosed file. However the data is not displaying exactly how I would like.
1/ I only want one monthly total for each 'letter' (Column A) rather than multiple July & August entries.
2/ As I add 'data' each week, how do I avoid having to recreate a new Pivot table to reflect the 'latest' results.
Any help is appreciated. K. (Using Excel 2002 SP3)

[meyero90]

I simply expanded your data source range to Data!$A$1:$L$65536, you'll just have to refresh your pivottable each time you add a value.
Otherwise, with this new data source range you'll never have to worry about the pivottable not picking the most recently added values

[DonkeyOte]

1/ I only want one monthly total for each 'letter' (Column A) rather than multiple July & August entries.

Right click on the Month and select Group -> I would advise Years & Months if your data is likely to cross multiple calendar years.

2/ As I add 'data' each week, how do I avoid having to recreate a new Pivot table to reflect the 'latest' results.

Use a Dynamic Named Range as source for the Pivot (don't reference the entire range as this is a hideously large cache)

Insert a Defined Name as follows:

Please Login or Register  to view this content.

Modify the PT source to be _PTData

[Beaneater]

1- Right Click the month header in your pivot table-> grouping and show detail -> Group. Set the start date to july 1st.
The Auto feature of Excel chooses the earliest date it sees and screws up the categories it seems.

2- You need to define a dynamic range that expands as the data grows.

-> Insert -> Name -> Define. Then Name it "Database" for example and refers to :

=OFFSET(Data!$A$1;0;0;COUNTA(Data!$A:$A);COUNTA(Data!$1:$1))

Click Add then ok

Now the name Database will always contain all the data present in your DATA sheet, no matter how many lines or columns you add.

All you have to do is use DATABASE as your pivot table's data source

Right click the pivot table and open the pivot table wizard. Click back once and replace

Data!$A$1:$L$91

by

=Database