Pivot Table Summary of Raw Data

[bee3902]

Hello! I am new to Pivot Tables but not new to Excel. We have a monthly allocation of a complex bill that needs to be completed by non-technical people in Accounts Payable.

We have 150 gas credit cards spread throughout our agency. One program might have 10 while another might only have 1 or two. We have a list of all the cards 1 to 150 along with their corresponding program. The bill detail gives us each card with the total fuel purchases. That data is typed into the rawdata sheet.

I did a Pivot Table which returned a simple Total of Purchases for each program which is great. Problem is that there is also a monthly fuel credit for paying the bill on time that also needs to be allocated. That allocation needs to be based on a percent of total.

I am trying to get a summary sheet created that shows: Col1: program Col2: Amt of Fuel Purchases (summary of cards for that location) Col3: Percent of Total Col4: Portion of CR based on % of Total for that location Col5: Sum of Fuel Purchases less the credit.

I would like to give direction to the operator - just copy over the previous months allocation workbook - type in the new rawdata from the bill for each card - save the workbook - reopen it and go to the summary sheet (that would refresh when reopened) - print out and do the allocation in our GL system.

Does anyone have a suggestion how this might best be accomplished? Or even if it can be accomplished?

Thanks so much for your time
Meg

[contaminated]

Upload an Example plz