Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.