Creating a Pivot Table

[AhmedAbdelhai]

Hi,
I'm trying to create a dashboard that plots Sales Vs TGT per Sales Rep.

I receive a sales sheet from the distributor every month similar to the sheet " Sales Sheet" in the attached workbook.

I only add the sales Rep column to it and map the accounts to the respective sales rep using a reference sheet and VLOOKUP.

Then I add this sales sheet to the previous month sales sheet.

The problem is:

In the reference sheet you can see 3 Sales Rep and Pool Sales. Any sales under pool sales should be divided by three and added to the sales reps so every one gets an equal share.

Also,

Every Sales rep has a TGT from each item. And some specific accounts have TGT from some items too.

I could do it but I think there must be an easier way to do it.

I firstly, make a pivot table that plots all sales from the 3 reps and the pool sales. The I created a new table where I put the reps TGTs and a sales cell. in the sales cell I have a getpivotdata function that pulls the sales of each rep + (the sales from the pool sales /3) frome the pivot table so I get sales per item per rep accurately.
Then I create a pivot table from that final table.

Similarly I do the same for the accounts TGT. But it is a very tiresome process as auto fill won't work with getpivotdata function. I have to do every cell seperately.

I hope someone could make sense of what I'm saying and help me..

Thank you

Sample sheet Gofile - File sharing platform, anonymous and free