Forum Discussion
Extracting data from pivot table and displaying in another table
Hi guys, I am trying to create a dynamic table (Table 2 in "Report" sheet) and fill field values from the data in pivot table in "Pivot 1" sheet. For instance when I select the region R1 using the dr...
Rockychap Changed the set-up of Pivot 1 and added dynamic GETPIVOTDATA formulae in the Report section. I think this is what you wanted to achieve. See attached.
With Excel 365 you can achieve the same results without the pivot table.
= LET(
selectedData, FILTER(Table2, Table2[Region]=selectedRegion),
area, INDEX(selectedData,,2),
volumeA, INDEX(selectedData,,3),
revenueA, INDEX(selectedData,,6),
priceA, revenueA/volumeA,
volumeLY, INDEX(selectedData,,5),
revenueLY, INDEX(selectedData,,8),
priceLY, revenueLY/volumeLY,
CHOOSE({1,2,3,4,5,6,7}, area,
volumeA, revenueA, 100*priceA,
100*(volumeA/volumeLY-1),
100*(revenueA/revenueLY-1),
100*(priceA/priceLY-1)) )The formula is rather long but, entered into cell C52, it generates the entire array of results.
- I also doing a similar project with extract data from pivot table. I noticed in your solution, the data for region (A1 to A6) do not have formulas. Hence, I assumed it's cut and paste. In my situation, the data in 'this' column varies. I know I have to use getpivotdata function, but I have no clue how to integrate it with a vlookup or index array formula. Any advice? tks.
