Forum Discussion
Automatically insert rows on worksheet linked to Pivot Table
I'm using Excel in Office 365. I have a worksheet linked to a Pivot Table in the same workbook. The worksheet is linked via cell references on the pivot table table. Here's the problem: Whenever new ...
Another option, since you are using Excel 365, is to use the SORT/UNIQUE functions on the source data to build lists of the candidate row and column headers.
You could then use the lists to pull data from the Pivot Table using GETPIVOTDATA by using them as an array of parameters. Errors returned by missing data rows could be replaced by blanks or filtered out.
IMHO, GETPIVOTDATA in particular and all cube formulas in general add load of headache if data source is updated in range. With data source pivoted in the same workbook and result returned / linked to another table Power Query could be more proper solution.