Hey guys,
I have a report that i submit every week which contains several pivots. I have included 1 pivot in the attached file. When additional records are added to the pivot source and even after refreshing, the Customer(All) field is not arranged in alphabetical order. This only works when the pivot is created the first time. All incremental records go one below the other and does not join with the existing records to arrange itself alphabetically. This Customer(All) field is a report filter as shown.
How do i ensure that this field gets sorted each time the pivot is refreshed? I also thought of deleting the pivot and re-creating a new one everytime data is added to the source. However, there are several other pivots and formulae that are connected to this pivot which will get messed up if i delete it.
Appreciate your help.
P.S - I have attached the file and the screenshot of the pivot field that requires sorting.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi,
The first thing you have to do is create a table of your data or define a dynamic name : each time you add a new data will be recognize by PT refresh.
The sorting is not possible on Report Filter area but you can apply the following procedure :
- Drag the Customer field in Row Labels area
- Sort the Customer field
- Drag back the Customer field to Report Filter area
Now the field is sorting according to your wishes and that sort remains active even after adding a new entry
You can find the complete procedure in Excel Pivot Tables
Hope this helps
Best regards
Thanks Jpr. This works however, in the current file, i have several calculations and other pivots that get affected if i do the switch in the fields.
Is there any other way this can be accomplished? This is urgent as i need to submit a report to my customer shortly.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks