getpivotdata() behaves differently depending on the order of fields

[skb]

Hi all,

I'm encountering a problem in getting data from a pivot table using two sets of arguments to getpivotdata(). Depending on how the Pivot Table is laid out, I sometimes get a #REF! with one or both the getpivotdata() calls.

I have a pivot table on a sheet with the following fields: Project, Cost Type, Cost Duration, Key, Min Cost, Max Cost. All fields are visible in the pivot table.

I use two variants of getpivotdata() in a 3rd sheet called 'Cost Summary':

variant A: getpivotdata(pivotTableVariable, "Sum of Max Cost", "Project", "X", "Cost Type", "Y", "Duration", Z)
variant B: getpivotdata(pivotTableVariable, "Sum of Max Cost", "Cost Type", "XYZ", "Cost Duration", "ABC")

Depending on the order of fields in the pivot table, one or both of these return #REF!. If I play around with the pivot table to move the fields around the right values pop up.

I thought that getpivotdata() worked as long as the data was visible and does not depend on the order of the fields.

Can you please tell me what I'm missing? Thanks a lot,
Sri

 

[RoryA]

I can't say I've ever noticed that, assuming that there is only one cell that matches the criteria you specify. Which version of Excel are you using? Can you post a sample workbook somewhere?

 

[skb]

Hi Rory,

Thanks. I will post a sanitized workbook shortly.

I wonder if my setup with pivots on pivoted data might be causing this:

1. The base data is in Sheet 1; it is based on vlookup()s and other formulae pointing to multiple other sheets
2. I create a pivot table (Pivot 1) in a separate sheet (Sheet 2) on the data in Sheet 1
3. I create a third sheet (Sheet 3) based on getpivotdata() on Pivot 1 and vlookups() from another sheet
4. I create a second pivot on Sheet 3 (Pivot 2) in the same sheet as Pivot 1 (Sheet 2)
5. I have a fourth sheet (Sheet 4) based on getpivotdata() of Pivot 2 (I do this mainly for aesthetic and copy/paste to PPT reasons)

Calls to getpivotdata() on both pivot tables act up so I wonder if both pivots being in the same sheet is an issue (don't see why).

Please advise; I am working on uploading a workbook. Thanks and best regards,
Sri