SOLVED

Extracting data from pivot table and displaying in another table

%3CLINGO-SUB%20id%3D%22lingo-sub-2397513%22%20slang%3D%22en-US%22%3EExtracting%20data%20from%20pivot%20table%20and%20displaying%20in%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397513%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%20I%20am%20trying%20to%20create%20a%20dynamic%20table%20(Table%202%20in%20%22Report%22%20sheet)%20and%20fill%20field%20values%20from%20the%20data%20in%20pivot%20table%20in%20%22Pivot%201%22%20sheet.%20For%20instance%20when%20I%20select%20the%20region%20R1%20using%20the%20dropdown%20box%20in%20%22Report%22%20sheet%2C%20I%20want%20it%20to%20display%20all%20the%20corresponding%20data%20from%20the%20pivot%20table.%20The%20format%2Flayout%20of%20the%20dashboard%2FTable%202%20must%20remain%20unchanged.%20Greatly%20appreciate%20if%20anyone%20can%20help%20me%20with%20this%20been%20struggling%20a%20lot%20with%20this!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2397513%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2397699%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20data%20from%20pivot%20table%20and%20displaying%20in%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397699%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1066281%22%20target%3D%22_blank%22%3E%40Rockychap%3C%2FA%3E%26nbsp%3BChanged%20the%20set-up%20of%20Pivot%201%20and%20added%20dynamic%20GETPIVOTDATA%20formulae%20in%20the%20Report%20section.%20I%20think%20this%20is%20what%20you%20wanted%20to%20achieve.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2397717%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20data%20from%20pivot%20table%20and%20displaying%20in%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397717%22%20slang%3D%22en-US%22%3EThanks%20a%20lot!%20Yes%2C%20this%20is%20exactly%20how%20I%20wanted%20it.%20I%20see%20what%20you%20have%20done.%20The%20format%20of%20the%20pivot%20table%20was%20the%20problem.%20Thanks%20again!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2397876%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20data%20from%20pivot%20table%20and%20displaying%20in%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1066281%22%20target%3D%22_blank%22%3E%40Rockychap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20Excel%20365%20you%20can%20achieve%20the%20same%20results%20without%20the%20pivot%20table.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20selectedData%2C%20FILTER(Table2%2C%20Table2%5BRegion%5D%3DselectedRegion)%2C%0A%20%20%20%20area%2C%20%20INDEX(selectedData%2C%2C2)%2C%0A%20%20%20%20volumeA%2C%20INDEX(selectedData%2C%2C3)%2C%0A%20%20%20%20revenueA%2C%20INDEX(selectedData%2C%2C6)%2C%0A%20%20%20%20priceA%2C%20revenueA%2FvolumeA%2C%0A%20%20%20%20volumeLY%2C%20INDEX(selectedData%2C%2C5)%2C%0A%20%20%20%20revenueLY%2C%20INDEX(selectedData%2C%2C8)%2C%0A%20%20%20%20priceLY%2C%20revenueLY%2FvolumeLY%2C%0A%20%20%20%20CHOOSE(%7B1%2C2%2C3%2C4%2C5%2C6%2C7%7D%2C%20area%2C%0A%20%20%20%20%20%20volumeA%2C%20revenueA%2C%20100*priceA%2C%0A%20%20%20%20%20%20100*(volumeA%2FvolumeLY-1)%2C%20%0A%20%20%20%20%20%20100*(revenueA%2FrevenueLY-1)%2C%20%0A%20%20%20%20%20%20100*(priceA%2FpriceLY-1))%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284814i7B10DECDB4594EE2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20formula%20is%20rather%20long%20but%2C%20entered%20into%20cell%20C52%2C%20it%20generates%20the%20entire%20array%20of%20results.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

4 Replies
best response confirmed by allyreckerman (Microsoft)
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.

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)) )

image.png

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.