Lookup on a Pivot table page field - problem

[gooniegirl180]

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

 

[gooniegirl180]

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