How to Capture data from EXCEL Pivot Table and display in shapes? - Part 1

Question

Greetings,

I'm creating a dashboard using Excel Pivot tables and charts. However, I'm facing a problem. In the Dashboard, I'm creating in a sperate sheet contains the dashboard layout. And in a 2nd sheet, I'm creating all PV tables that are the sources for the dashbaord' charts or data cards.

Problem No. 1 I'm facing is that I can't populate the data from PV table to the data cards, which are box shapes inserted and formated to display the PV table output. I select the data card box, and the enter "=" and go the PV table sheet and select the desired PV output field for example "pending" value. However I got an error message (see screenshot No. 3). But if I created in the PV table same sheet cells and use the same folmula, then these cells will refelct the PV table output. and then I can refrence these cells from the shapes and display the values.

How can I extarct the fields values directly from the Pivot tables without the need to creating other value-holders cells and refrence them?

As Illustred below.

1-Below is PV table Sheet

2- Below is Dashbaord Sheet

3- this is the function used to get the PV table value. It's not working cross sheets, but work with the same sheet. as shown in screen shot No. 1.

Answer 1

You could enter the GETPIVOTDATA formula in a cell 'behind' the shape (or any other cell out of view) and link the shape to that cell.

Answer 2

Thank you Riny, This tip I know, and I've done. The issue I have many of these data cards and it would a very lengthy to use this tip. You can see I use it in screenshot No. 1.

Answer 3

You may try povit chart. But it need one chart for one povit table. Though you can use report connection to control filters, it still needs much time to create povit charts.
The workaround you did is the easiest way for now.

You may send feature request here.

Excel · Community (microsoft.com)