Forum Discussion
Automatically insert rows on worksheet linked to Pivot Table
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.
SergeiBaklan I am perfectly content to go with your opinion on the matter. I only very rarely get involved with data analysis and on those occasions that I do, I spend too much time fighting with PT defaults that I don't want. Added to that, I always forget the need to refresh the data at least once.
What I did remember was that GETPIVOTDATA (now working as a dynamic array formula) was more reliable than trying to address cells directly and gave me scatter charts as well as bar charts that held their formatting when series disappeared. Now I tend to ignore pivot tables and use dynamic arrays but that is merely a reflection of the work I encounter, given the need or opportunity I rather like exploring Power Query.
PeterBartholomew1 SergeiBaklan Wyn Hopkins @
Hey Guys, Thanks for the suggestions! I'll spend some time giving them a try over the weekend and let you know what happens.
Thanks!!!
