Forum Discussion

Rockychap's avatar
Rockychap
Copper Contributor
May 29, 2021

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's avatar
      Rockychap
      Copper Contributor
      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!
  • 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.

    • choongko's avatar
      choongko
      Brass Contributor
      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.

Resources