Combining Pivot table with other lookup data

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
125
I have a pivot table that contains sales data including region. I have a separate table that contains the sales goals by region and changes periodically. My current solution is to place the goals outside of the sales pivot data, but as the data changes the two become misaligned. (perhaps more regions are added) Does anyone have a technique that would put the goal data in the pivot without replicating it?

Here is a simple example:
Region Goals
RegionGoal
North$ 100,000
South$ 250,000
East$ 500,000
West$ 100,000
Sales Data
SalespersonRegionItemAmount
JoeNorthCar$ 50,000
MarySouthBoat$ 40,000
SallySouthBoat$ 30,000
FreddEastCar$ 70,000
SamWestCar$ 70,000
BartWestBike$ 10,000
JaneNorthBike$ 10,000
IreneSouthCar$ 80,000
Pivot DataLookupMath
Row LabelsAmtGoalDifference
East
70000​
$ 500,000$ 430,000
North
60000​
$ 100,000$ 30,000
South
150000​
$ 250,000$ 180,000
West
80000​
$ 100,000$ 30,000
Grand Total
360000​
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You can probably achieve your desired results using Power Pivot. Also, the Difference column in your example seems a bit off.
 
Upvote 0
What I am really looking for is a suggestion for a solution. Thanks for pointing out the error in the difference. Here is the data again:


Region Goals
RegionGoal
North$ 100,000
South$ 250,000
East$ 500,000
West$ 150,000
Sales Data
SalespersonRegionItemAmountGoal
JoeNorthCar$ 50,000
100000​
MarySouthBoat$ 40,000
250000​
SallySouthBoat$ 30,000
250000​
FreddEastCar$ 70,000
500000​
SamWestCar$ 70,000
150000​
BartWestBike$ 10,000
150000​
JaneNorthBike$ 10,000
100000​
IreneSouthCar$ 80,000
250000​
Pivot DataLookupMath
Row LabelsAmtAverage of GoalDifference
East
70000​
500000​
$ 430,000
North
60000​
100000​
$ 40,000
South
150000​
250000​
$ 100,000
West
80000​
150000​
$ 70,000
Grand Total
360000​
218750​
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,374
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top