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 dropdown box in "Report" sheet, I want it to display all the corresponding data from the pivot table. The format/layout of the dashboard/Table 2 must remain unchanged. Greatly appreciate if anyone can help me with this been struggling a lot with this!
Cheers!
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.
- Riny_van_EekelenPlatinum Contributor
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.
- RockychapCopper ContributorThanks a lot! Yes, this is exactly how I wanted it. I see what you have done. The format of the pivot table was the problem. Thanks again!
- PeterBartholomew1Silver Contributor
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.
- choongkoBrass ContributorI 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.