Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

GETPIVOTDATA function return error

Copper Contributor

Hi,

I am trying to make a dashboard with different charts taking information from pivot tables and connected to a slicer filter to have a coordinated view.

for the number in the filed I am trying to use Getpivotdata function because if I just put the connection to the cell when I make the filter the information are not correct

DR_74_0-1705481713201.png

But when I connect the formula return me the following message 

DR_74_1-1705481796140.png

the dashboard is in a different sheet than the pivot (but also with the pivot in the same sheet is not working)

DR_74_2-1705481894522.png

I don't understand what is missing :(

 

thanks

 

 

5 Replies

@DR_74 You can't 'attach' a formula like that to a shape. consider entering the GETPIVOTDATA formula in a cell near the pivot table and out of view of your dashboard and reference that cell in the shape. Example below.

Riny_van_Eekelen_0-1705485489862.png

 

 

@DR_74 

Return GETPIVOTDAT result into any cell and for the shape use reference on that cell.

@Riny_van_Eekelenand @Sergei Baklan 
thanks for the clarification, as you both suggested I have done a connection with a normal cell and is working.
As you know how to use the GETPIVOT formula, how can I add the  "-" to don't have #REF! when the value is = 0?

DR_74_0-1705499755458.png

thank you very much for the help!

 

@DR_74 

You may wrap it with IFERROR()

 

=IFERROR( GETPIVOTDATA(...), 0 )

I assume you have #REF on filtered labels, for other ones GETPIVOTDATA() returns correct values.

 

 

Ok! so I can use GETPIVOT in normal formula I though was a special formula.
sorry I didn't think about it.

thank you!