• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with GETPIVOTDATA returns #REF! error

Hello,

I'm not able to find the solution to the following formula. I keep getting a #REF! error.
=GETPIVOTDATA("Sum of Actual",'PIVOT-SP'!$A$3,"QTR Ends",DATE(YEAR($D$2),((INT((MONTH($D$2)-1)/3)+1)*3)+1,1)-1,"Budget Category","General")

If I remove field2 and item2 ("Budget Category","General"), like this:
=GETPIVOTDATA("Sum of Actual",'PIVOT-SP'!$A$3,"QTR Ends",DATE(YEAR($D$2),((INT((MONTH($D$2)-1)/3)+1)*3)+1,1)-1)

I remove all proprietary information and uploaded a file here as an example.

Thank you in advance!
 

Attachments

  • GETPIVOTDATA Formula.xlsx
    33.2 KB · Views: 6
I do not use pivot tables so others will be able to provide more authority. My observation is that individual figures for the Budget Category are not available because it appears as a Report Filter rather than a row or column category.
 
The significant change is the layout of the Pivot Table.
I have also introduced arrays wherever possible in the output, but that is because I am an Excel 365 user and it most likely does not apply to you.
 

Attachments

  • GETPIVOTDATA Formula.xlsx
    35.9 KB · Views: 10
Hi,

Your formula refers to "Budget Category";"Special". But you do not have this field inside your pivot coordinated fields (row/column). Hence this intersection can't be found and this returns the error.

It does work when you put the field in the row section of the pivot.
Then your other formulas stop working. Because there is no single intersect matching.

In general when I used to do similar things...
- hidden sheets would store many simple pivots (sometimes up to a 100 or even more) on the same data with single row/column coordinated fields. So no hierarchies of fields and subtotals.
- large data would be kept in external files
- formula referred to one of these pivots, often using grand totals as well
- getpivotdata formulae are on a technical sheet, providing me all metrics required, be it for tables or charts
- on the report, do some simple index/match (or nowadays that could be xlookup)
- adding slicers to make this dynamic (if that would be required) and play with the connection settings to allow calculations where nowadays one could argue DAX is a better option and I would agree. Back then that was not an option for me.

It's in a nutshell how got around the issues with the worst function in Excel ever and actually fell in love with it. (kind of..:rolleyes:.)
 
The significant change is the layout of the Pivot Table.
I have also introduced arrays wherever possible in the output, but that is because I am an Excel 365 user and it most likely does not apply to you.
Ok, so here's something weird. When I open your revised file, I can see that the results are fixed. However, when I click Enable Content, the results revert back to #REF!. I did have an external link that wasn't needed. So, I deleted it but it didn't fix the results. Here's the file attached.
 

Attachments

  • GETPIVOTDATA Formula_2.xlsx
    34.4 KB · Views: 5
Hi,

Your formula refers to "Budget Category";"Special". But you do not have this field inside your pivot coordinated fields (row/column). Hence this intersection can't be found and this returns the error.

It does work when you put the field in the row section of the pivot.
Then your other formulas stop working. Because there is no single intersect matching.

In general when I used to do similar things...
- hidden sheets would store many simple pivots (sometimes up to a 100 or even more) on the same data with single row/column coordinated fields. So no hierarchies of fields and subtotals.
- large data would be kept in external files
- formula referred to one of these pivots, often using grand totals as well
- getpivotdata formulae are on a technical sheet, providing me all metrics required, be it for tables or charts
- on the report, do some simple index/match (or nowadays that could be xlookup)
- adding slicers to make this dynamic (if that would be required) and play with the connection settings to allow calculations where nowadays one could argue DAX is a better option and I would agree. Back then that was not an option for me.

It's in a nutshell how got around the issues with the worst function in Excel ever and actually fell in love with it. (kind of..:rolleyes:.)
I appreciate your response and I'm sorry, but I'm a little lost on what you're recommending.
 
Basically, to use GETPIVOTDATA, the data you are getting has actually to be showing on the pivot table. I had some problems with the workbook trying to access external data but the main issue was the page filter that needed to be moved to column or row headers.
 
I've made sure there are no references to external data. In the attached, I now have three tabs: SPENDINGPLAN (data), PIVOT-SP-1 (original) and PIVOT-SP-2 (the one you created). In your comments, you say "the data you are getting has actually to be showing on the pivot table". So, I cleared all filters on both pivot tables. On the SPENDINGPLAN worksheet, you can see the results. Notice both Pivot tables return the same results. It's a little better, but not completely fixed. Thanks!
 

Attachments

  • GETPIVOTDATA Formula_07-11-21.xlsx
    48 KB · Views: 5
Back
Top