Referenced values in a pivot table

Question

I have a workbook that is being used by management to review data.

One sheet has a pivot table with some quite specific information.

The pivot table has multiple fields being pivoted in the rows. It is set up the standard expand/collapse view is enabled. It is thus possible to expand items to view specific data. This is ideal for management as they want to generally view the overarching numbers, and occasionally drill to the detail.

On a separate sheet I am referencing this to form a results table. I am referencing various rows, including those "drilled down" areas for this report.

This worked great until I handed the sheet over, and I've discovered that if you collapse all the fields in the pivot table, the GETPIVOTDATA() function stops being able to reference those rows hidden rows.

How do I stop this occurring? Surely excel can't be so badly designed that "hiding them" literally makes them unreferencable?

FYI: Excel 2k7 on W7.

Answer 1

Unfortunately, Excel is exactly that "badly designed" in this case. From the following link you can see this behavior is the correct behavior:

http://office.microsoft.com/en-001/excel-help/getpivotdata-function-HP010342556.aspx

If the arguments do not describe a visible field, or if they include a report filter in which the filtered data is not displayed, GETPIVOTDATA returns #REF!.

What you'll have to do is copy and paste the pivot table with whatever filters you have either on a new sheet and reference that, if you need the pivot to be intact, else can paste the pivot table on top of itself to destroy it and prevent people from modifying the filters.