Extracting data from pivot table and displaying in another table

[Rockychap]

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!

[#2 · Accepted solution]

@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.

[#3]

Thanks 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!

[#4]

@Rockychap 

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.

[#5]

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.