Help with GetPivotData

[Gjg1234]

Hi,

I cannot seem to get all parts of my formula to calculate.

I have created the formula for 243 rows, but even though the formula is supposed to calculate correctly for all rows, it seems to work for some rows but not for the rest where it is supposed to calculate.

My formula is as follows:

Works for this:

=GETPIVOTDATA("Count of Product Code",Pivot_Analysis!$A$3,"Total No of Orders",B2,"Total no Qty Ordered",C2,"Total No of Days",D2,"Total No of Order Lines",E2,"Total No of times picked from a Pallet Location",F2)

But not this (even though it is supposed to):

=GETPIVOTDATA("Count of Product Code",Pivot_Analysis!$A$3,"Total No of Orders",B2,"Total no Qty Ordered",C2,"Total No of Days",D2,"Total No of Order Lines",E2,"Total No of times picked from a Pallet Location",F2)

Formula Sheet:

[/IMG]

Reference Pivot Table:

[/IMG]


Any ideas on how to fix this?

 

[RoryA]

Those formulas look exactly the same to me. What's different?

You will get a #REF error if there isn't a cell in the pivot table for the conditions you specify - e.g. your row 3.

 

[Gjg1234]

Sorry, would you mind clarifying what exactly you mean by that?

 

[RoryA]

Your row 3 is looking for a combination that doesn't appear in your pivot table, hence the error. There is no Low entry for "No of times picked from a Pallet Location" within the High entry for "No of Order Lines" in your pivot table.

 

[Gjg1234]

Thanks for clarifying.

However, for this one:

[/IMG]

in row 123, despite the value supposedly being 6277, it does not seem to register. Any ideas as to why?

 

[RoryA]

Perhaps one, or more, of the "Low" values is actually "Low " or " Low" with additional spaces? Type = and select that cell and see what the hardcoded GETPIVOTDATA formula is.

 

[Gjg1234]

That does't seem to be the case, I checked. Is there any other error that could be related to this? I read that the getpivotdata formula can be pretty buggy

 

[RoryA]

Only if you are using custom subtotals, in which case I think all the formulas would error. What was the GETPIVOTDATA formula that Excel produced for you?

 

[Gjg1234]

I am not, I checked that. What exactly do you mean by your second sentence?

 

[RoryA]

I mean if you type an = sign and then select the cell in the pivot table from which the value should be returned, Excel should create a GETPIVOTDATA formula for you (unless you turned that option off in settings). What is that formula?