# Lookup on a Pivot table page field - problem

#### gooniegirl180

##### Board Regular
I have a pivot table which lists costs by type. One of the page fields of the pivot table is "Site".

Above the pivot table, I have an area which takes the total costs from the pivot, then underneath that I have "Production Tonnes", which is a lookup: it returns the tonnes manufactured by the site selected on the page field. If no site is selected on the page field (ie the page field value is "(Mulitple Items)"), it returns the total aggregated production tonnes. From these two figures, I can calculate a Cost per Tonne. This works great. Formula for Production Tonnes is:

=IF(\$B\$8="(Multiple Items)",'Production Tonnes'!B2,VLOOKUP(B8,'Production Tonnes'!\$A\$4:\$C\$44,2,0))

where \$B\$8 is the pivot table page field for site,
and 'Production Tonnes'!B2 is the aggregated total of all sites' production tonnes.

Of course, a senior manager here has found a problem. If he hides the values of some of the sites (but not all), the costs will all change in line with his selections, but as the page field value for "Site" is still "(Multiple Items)", the Production Tonnes value returned in the lookup is the total aggregated tonnes across all sites instead of just the tonnes manufactured at "unhidden" sites. This applies even if I drag the "Site" page field down to the pivot table and select only three sites then drag it back up to be a page field again.

I know a vlookup won't cut it once you want to aggregate some but not all of the sites. But what I can't figure is how to access the "list" of items in the page field so I know which tonnes to aggregate.

Any ideas? I'm using Excel 2003.

Regards,
gooniegirl180

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### gooniegirl180

##### Board Regular
I'm still struggling with this one....

Anyone able to tell me how I can access the list of sites in the Pivot Table Page field once some of them have been hidden or filtered out?

Regards,
gooniegirl180

Replies
0
Views
216
Replies
1
Views
246
Replies
2
Views
2K
Replies
4
Views
718
Replies
14
Views
4K

1,190,866
Messages
5,983,296
Members
439,836
Latest member
BuckyBoyRx

### 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.

### Which adblocker are you using?

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

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