GETPIVOTDATA with multiple data_fields

[Bendy]

Hi,

I'm new to this forum, and I'm hoping you can help me with an issue regarding the GETPIVOTDATA function.

In Excel 2010 I've organized some sales data by month, product and country. I've started using the GETPIVOTDATA function to pull monthly year to date sales data, and after quite some investigation I found the syntax for selecting multiple data fields.

This is an example of the parameters I'm using when I want to see the total sales for January, February and March 2011 for country DK.

Data_field: {" Act 01.11";" Act 02.11";" Act 03.11"}

Pivot_table: DataPivot!$A$6

Field1: "Country"

Item1: "DK"

Then my formula looks like this:

SUM(GETPIVOTDATA({" Act 01.11";" Act 02.11";" Act 03.11"};DataPivot!$A$6;"Country";"DK")

The above formula works fine, but I can't find the correct syntax to get the value for the data_field parameter from another cell, if I need multiple values. It works fine with single values for example like this:

SUM(GETPIVOTDATA(" "&Parameters!$A$1;DataPivot!$A$6;"Country";"DK")

where Parameters!A1 = Act 01.11

So I need to know what content I should have in Parameters!A1 and what the syntax in the function GETPIVOTDATA should be if I want to get multiple data fields, for example {" Act 01.11";" Act 02.11";" Act 03.11"}.

I've tried in all kinds of ways over the last few days, but with no luck. I think the tricky parts are the {} and the fact that there should be several quotes within the sentence.

I hope the above makes sense, otherwise please ask and I will try to provide more details.

Any help you can provide will be very appreciated.

I hope I put this in the right forum, because I realize there's also one for Pivot Tables, but as this is a question about a formula I thought this one was more appropriate.

/Bendy

[Ken Puls]

Hi Bendy, and welcome!

I am going to move this to the PivotTable forum, just because GetPivotData is so closely related.

I'm curious if you might be able to upload a stripped down workbook with no confidential information in it, just so we can see how you laid out your data... I think I have a solution for you, but to get it right would involve seeing the exact headers for rows and columns.

[Bendy]

Hi Ken,

Thanks for your quick reply. I've made a quick and simplified version of my file and attached it here.

A quick description of the file:

Report contains the sales by current month and year to date, using GETPIVOTDATA formulas. I've included both the formula with fixed values that works, and the one with cell references (via VLOOKUP) that doesn't work.

Data contains the raw data.

DataPivot contains the pivot table.

Parameters contains the parameters used for the period selection via VLOOKUP.

I've highlighted the cells that are causing problems in red (both the formulas and the referenced parameters).

Please let me know if you have any questions.



Another issue by the way: Even when using fixed values I can only use either multiple data_fields or multiple fields, so either multiple months or multiple countries. That's why in the YTD formulas i've used two GETPIVOTDATA, one for each country. If I use both in the same formula it returns an error.

Do you know if it's possible to make multiple selections for both in the same formula, or do I need to use multiple formulas?



Thank you very much for your help.

[Ken Puls]

How many products/countries are you going to end up with overall? Lots of each?

To be honest, my first temptation here would be to create a second Pivot Table that summarizes by product type, then country, add totals and pull it out that way. (Having said that, one of the Pivot Table gurus will probably give me a little grief for suggesting is, as I'm sure there is a more efficient way.)

The issue I'm seeing here is that you have the product showing up twice in the table (so far), and it could be many more times...

For summing multiple columns we can use an Index Match combination. (The one in the file attached looks pretty ugly, but you could shorten it with some named ranges, and by breaking the Match components out to helper cells.)

Example attached.

[Bendy]

Thanks for your suggestion. The formula would need to be updated though to check if the start and end months are the same, otherwise in January I get double values. An IF sentence should solve this though.

By the way there are 3 countries and around 100 products, so there's more data in the full version.

If you come up with the solution to selecting multiple data_fields with GETPIVOTDATA via cell references I'm still very interested as I prefer to maintain just one PIVOT table and I like the flexibility of the GETPIVOTDATA method.

Thank you very much for all your help so far.

[Roger Govier]

Hi

Your problem is that your data is not in the proper normalised form for generating Pivot Tables, and you are making life difficult for yourself.

Your data should have just 5 headings, and not grow as you add more periods.
Country, Area, Product, Period, Value

The attached worksheet has a macro that will convert your data into a normalised form, as below

Sub NormaliseData()
    Dim wsS As Worksheet, wsD As Worksheet
    Dim lr As Long, lc As Long, i As Long, j As Long, k As Long

    Set wsS = Sheets("Data")
    Set wsD = Sheets("Data2")

    lr = wsS.Cells(Rows.Count, 1).End(xlUp).Row ' number of rows of data
    lc = wsS.Cells(1, Columns.Count).End(xlToLeft).Column ' last column populated
    
    wsD.Range("A1") = "Country"  ' fill headings on new sheet
    wsD.Range("B1") = "Area"
    wsD.Range("C1") = "Product"
    wsD.Range("D1") = "Period"
    wsD.Range("E1") = "Value"
    
    k = 2  ' first row of data to be written on new sheet
    
    For i = 2 To lr ' start from row 2 of source data to the end
        For j = 4 To lc  ' first three columns are standard, but loop for each column
                               ' from column column 4 to last column
            wsD.Cells(k, 1) = wsS.Cells(i, 1)
            wsD.Cells(k, 2) = wsS.Cells(i, 2)
            wsD.Cells(k, 3) = wsS.Cells(i, 3)
            wsD.Cells(k, 4) = wsS.Cells(1, j)
            wsD.Cells(k, 5) = wsS.Cells(i, j)
            k = k + 1
        Next j
    Next i

End Sub

Now with this as the Source data, you can create a Pivot Table which will show Grand Total by Row, as well as by column, so there is no need to sum ranges of values with GetPivotData.

I think it is also easier to do your Vlookup once, rather than in each formula so I have created a second report area on your sheet Report. The month selected via dropdown is in B12, and in B11 I have the single Vlookup formula to give the column heading from the PT.

Your formulae then simplify to

for each Product for the Month
=GETPIVOTDATA("Value",Pivot2!$A$3,"Product",$A15,"Period",$B$11)

for the Total for the month
=GETPIVOTDATA("Value",Pivot2!$A$3,"Period",$B$11)

for YTD for each Product
=GETPIVOTDATA("Value",Pivot2!$A$3,"Product",$A15)

for YTD Total
=GETPIVOTDATA("Value",Pivot2!$A$3)

[Bendy]

Hi Roger,

Thanks for the solution you provided. Unfortunately my original data sheet is not only used for this pivot, but another pivot / dashboard as well. Rearranging the data could be done with your macro, but redoing the other pivot / dashboard would be a longer task though. I have not created the dashboard so it would take quite some time to understand it fully and redo it.

Also for other purposes it's convenient that the data is organized like this, as it's easier to read the table if I want to check something directly in it.

[Roger Govier]

Hi

Well if that is the case, don't use GetPivotData for your YTD figures.
In column O of your Data Pivot sheet (assuming you are going to have 12 months of data) use
=SUM(C2:N2) and copy down.

I moved your second pivot so it was underneath the first. In reality, I would put them each on separate separate sheets to avoid the possibility of overlap as the data grows.
My second Table therefore started in row 20 and the formulae for the YTD figures become

=INDEX(DataPivot!$O$21:$O$28,MATCH(Report!A15&" Total",DataPivot!$A$21:$A$28,0))

copied Down

for the Total, either
=INDEX(DataPivot!$O$21:$O$28,MATCH("Grand Total",DataPivot!$A$21:$A$28,0))

or simply
=sum(C15:C16)

See attached revised file

[Roger Govier]

Hi

If you are likely to have data beyond the month for which you select as current month, then use the formulae as shown in the attached file.

[Bendy]

Hi Roger,

Sorry for the late reply, but I haven't had much time to check here or to progress with my Excel issue. I will try to have another look soon and see if can use your last suggestions.

Thanks a lot for your help.