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.