GETPIVOTDATA() #REF! Error

[goss]

Hi all,

Using Excel 2010

My GETPIVOTDATA() formula is returning a #REF! error.
Odd, because this works

=GETPIVOTDATA("PortionScore",$D$11,"Account","Total Revenue","SubCategory","Merchandise")

But when I change to this, I get the error :

=GETPIVOTDATA("PortionScore",'4L'!$D$11,"Account",$B3,"SubCategory",AF$3)

I have checked and rechecked the values in $B3 and AF$3 - they are definitely correct

What am I missing?
Thanks
w

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[goss]

Norie,

Sorry, I cannot, the data is proprietary.

Thanks,
w

[FDibbins]

Then can you create a dummy file that simulates your data?

Perhaps try changing 1 thing at a time in that formula (it looks like you changed at least 3), then maybe you can narrow down what the cause is?
Check for leading/trailing spaces in your data/names ranges/sheet names

[goss]

Norie / FDibbins,

I uploaded a sample file that closely approximates my process
The formulas work as did my formulas originally but now for reason one work and one does not

Thanks
w

[Norie]

Little bit confused, all the GETPIVOTDATE formulas in the workbook work fine and, as far as I can see, returning the expected results.

[FDibbins]

Thnanks Norie I was going to say the same thing

[goss]

Agreed, hence my confusion.
In the production workbook the formula with form1.2 works, the formula with form1.3 returns the #REF! error.

What's more, the formula with form1.3 used to work correctly, now returns #REF! error

I deleted the pivot tables and rebuilt them, same result.

Do you have any other ideas of things I can check?

Thanks,
w

[Norie]

Leading/trailing spaces (or other non-printing characters) in the data, values being used in the formula and/or sheet names.

Which version of Excel do you have?

[JosephP]

do you use any custom subtotals?

[goss]

Norie,

Excel 2010 32-bit

JP,

No, no custom subtotals, no calc'd fields

Thanks
w

[JosephP]

kinda hard to say then since your sample works. if you type = then select the cell you want does the automatic GETPIVOTDATA formula work? if it does then something doesn't match when you alter the formula

[goss]

Thanks JP,


Yes, when enter = and click on a grand total item for a company I get the "Full" formula
I then copy the formula to my summary page, add the sheet reference and replace any hardcoded criteria with cell references to
where the criteria are stored on the summary worksheet and add absolute references as appropriate.

Absolutley driving me insane, especially since a Getpivot formula works for PivotA.Form.Company
But as soon as I change to FormB the formula fails - all else equal

I have checked the name of the form at least 10 tiems no to make sure it is exactly the same as the Pivot (It is)
I copy pasted the the form from the Pivot Table to the summary worksheet
I have rebuilt the Pivot table - same result

Any other ideas?
Thanks
W

[Norie]

What happens if you start on the sheet where you want the formula to end up and not the one with the pivot table?

[JosephP]

have you narrowed down which criterion change causes the formula to fail by changing just one at a time?

what's the original formula and what did you change it to?

[goss]

Norie / JP,

Solved!

Per Norie's advice I added a new sheet and created a brand new Getpivot() formula
To be able to easily reference the correct criterion, I copied them from the Pivot Table and pasted to Notepad and parked on my 2nd monitor
I then went to the new tab and entered the formula and copy/pasted the criterion from Notepad to cells A1 and A2 respectively.

Imagine my surprise when I received the answer I was looking for!
I then pasted the criteria for the form name over the offending form name on the original summary worksheet and everything worked like a charm.

I still think it is odd that it works though.
If I look at the original criteria in the formula bar, there are no trailing spaces.

The only thing I did different this time was pasting to Notepad, then copy/paste from Notepad to Excel

Thanks for hanging in with me.
Kind regards,
w

[Norie]

I was actually suggesting starting the formula in an existing worksheet rather than doing it on the worksheet with the pivot and then copying over.

By starting on an existing worksheet I mean entering = in a cell, then selecting the sheet with the pivot table and selecting a value in the pivot table.

Anyway, if what you are doing works, it works.

[austin1chase]

I had the same problem - found the solution!! I was trying to change the reference of the month of the pivot table to a dynamic cell of mine, in this case 9, for September. I experimented with referencing other portions of the getpivotdata formula to a different cell. I did it with the formula parts that refer to a word, in this case "Direct", and the formula worked. So i thought maybe it had to do with text. So i formatted the next cell to text, then typed in my problem reference, which was the number of the month, in this case 9, and voila - it worked!!!!