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

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