Help with Pivot Tables Show Report Filter Pages

Hi,

Perhaps you can help me with this.

1. I have some sales transactions raw data in a table, on a sheet called "MonthlySalesReport".

I created a pivot table and sorted it by the salesperson's initials in the filters column of the pivot table. This worked great. This created a pivot table on a sheet called PivotTableReport. This shows each vendor's initials, name, item sold, date sold, and method of payment.

I then went to PivotTable tools--->Analyze---> Options--->Show Report Filter Pages and it generated a separate summary for each vendor based on their initials. Each new page was titled whatever the initials were.

Later on I added a new vendor to the MonthlySalesReport" page and clicked on refresh. It did update the PivotTableReport, and it showed the vendor's initials, but did not add a new initials sheet. I tried to click on PivotTable tools--->Analyze---> Options--->Show Report Filter Pages, and that did update the vendor sheets but it also created duplicates of the old ones, which I do not like because I had separate formulas for commission for each vendor, and would have to retype them all.

Is there any way to just automatically generate the missing vendor's page?

2. Some vendor's get special commission when a product is over $200. The way it works is that if a vendor sells a product that is under $200 then they get 80% of the product sold. If it is over $200 then they get 90%.

On each vendor's initial pages I have an automatic total of all the sales for the month for their gross sales $. Is there a formula to easily assign the appropriate commission amount based on each product sold?

3. If I want to add a separate formula to each vendor, is there a way to add it to the initials pages at the bottom of the pivot table and make it so that it does not get overwritten when I expand the table's drop down menus?

Thanks a lot guys and gals!

Version of excel: 2013.

Hi, Post a sample in excel