If you’ve found this page via Google or another search engine, you are likely to be irritated with Excel’s =GetPivotTable() function which arises when trying to reference a call within a pivot.
There are two problems with =GetPivotTable().
- It is hard to trace – a lot harder than seeing =A1 or similar
- If you are using a formula which references a pivot in an external file, then your formula will return a #REF! error when that source file is not open.
Fortunately there is a solution. In fact, there are two solutions.
- The manual method – The first option is that you can click a cell within the same tab as the pivot to return a normal cell reference, and then edit the formula to reference the correct cell.
- The better and automatic method – Alternatively, you can turn off the =GetPivotTable() function by clicking Analyse in the top tab (Note: you need to have clicked on the pivot table to see this option), then click the Options dropdown and untick ‘Generate GetPivotData’. Once unchecked, the irritating difficulties of linking to pivot tables should disappear once and for all.