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
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