Formula needed for a pivot table which takes information from another sheet

Occasional Visitor

Currently I have a pivot table on one sheet of my workbook (Sheet1) and a regular table on another sheet of the same workbook (Sheet2). I need to be able to type into Sheet2 and have it auto populate into Sheet1 (the pivot table) without directly referencing the cells I type in on Sheet2.
Sheet1 and Sheet2 both have two columns which states their favorite food in one column and favourite colour in another. After those two columns there are 10 columns of different cities where a number is placed on how many people have those same favorite food and color in that city. For example: the first two columns would read Pizza and Blue and then under Toronto the number would be 7 (but repeat that for ten different cities). Example is listed below.

 

Favorite

Food

Favorite ColorTorontoLondonKingstonPort HopeNorth BaySudburyTimminsGuelphWaterlooWindsor

Pizza

Blue6301907003

Sushi

Red1073480921

 

Now I want to be able to alter this file (the example shown above, which is Sheet2) and have the same values be populated into Sheet1 (which looks identical to Sheet2 but it is a pivot table). Because Sheet1 can be filtered by the first two columns, if I were to directly reference the cell from Sheet2 when the filtering would be put in place for the two title columns everything would shift out of place.

 

Any help on this would be greatly appreciated!

0 Replies