Using a reference cell in getpivotdata

[Satos]

Hi everybody

I'm new to the forum and kind of newbie with excel, and just for your information I have no knowledge of macro or VBA.

I am trying to prepare a reporting for weekly sales, and I use many pivot tables in my calculations. Until now I have build formulas using getpivotdata function where I didn't need to add the data source to the data model. Every column represents another week in my reporting sheet and the rows show gross revenue, net revenue and net net revenue...
Since I need to copy and paste a range in column to another column it was really useful for me to use a reference cell that has the date.

In cases where I use a pivot table where the data source is not added to data model, I was able to change the formula from:

=GETPIVOTDATA("Sum of Net revenue",'Orders Reporting Pivot'!$F$3,"Week of (order date)",DATE(2014,1,13),"Channel","Shop")

to

=GETPIVOTDATA("Sum of Net revenue",'Orders Reporting Pivot'!$F$3,"Week of (order date)",C2,"Channel","Shop")

Where C2 has the date in 10/03/14 format.

Now for some detailed calculations i need to use a pivot table where I have to add the data source to data model, and the getpivotdata formula that I get this time is not similar to the one that I have worked with:

=GETPIVOTDATA("[Measures].[Distinct Count of Order Number]",'Orders Reporting Pivot'!$A$3,"[SADatabase].[Week of (order date)]","[SADatabase].[Week of (order date)].&[2014-03-10T00:00:00]","[SADatabase].[Channel]","[SADatabase].[Channel].&[Shop]")

I have tried many variations (without any knowledge on what to do) but I wasn't able to refer to a cell.
I just need to change the date field item in the formula so when I copy and paste the formula to another cell in different column it will automatically change from C2 to D2.

Thanks for your help in advance

 

[GlennUK]

How about replacing:
2014-03-10
with
"&TEXT(C2,"yyyy-mm-dd")&"

 

[Satos]

Thanks GlennUK

Works perfectly!!! You made my day!!

 

[RoryA]

What did you actually try? I would expect Glenn's suggestion:

=GETPIVOTDATA("[Measures].[Distinct Count of Order Number]",'Orders Reporting Pivot'!$A$3,"[SADatabase].[Week of (order date)]","[SADatabase].[Week of (order date)].&["&TEXT(C2,"yyyy-mm-dd")&"T00:00:00]","[SADatabase].[Channel]","[SADatabase].[Channel].&[Shop]")

to work.

 

[Satos]

I have tried this:

=GETPIVOTDATA("[Measures].[Distinct Count of Order Number]",'Orders Reporting Pivot'!$A$3,"[SADatabase].[Week of (order date)]","[SADatabase].[Week of (order date)].&["&TEXT(C2,"yyyy-mm-dd")&"T00:00:00]","[SADatabase].[Channel]","[SADatabase].[Channel].&[Shop]")

Sorry I was to excited and forgot to share the result

 

[drdrae]

how do I go about doing this when my reference cell is text (a name of a person) that is contained in a pivot table made through powerpivot.

i've tried using the same idea as above and using lower() and trim() but cant get a result

 

[RoryA]

GETPIVOTDATA works the same with PowerPivot pivot tables too. What's the formula if you allow Excel to create it, and what did you change it to?

 

[drdrae]

thanks for the response RoryA:

im trying to modify this formula so it looks up the text in cell L14 (EX534) - which is the text located in the Pivot Table:

=GETPIVOTDATA("[Measures].[Sum of Hours]",$L$3,"[tbl_MOD].[Shift]","[tbl_MOD].[Shift].&[Day]","[tbl_MOD].[Name]","[tbl_MOD].[Name].&[EX534]")

tried changing the above formula concept to this:
=GETPIVOTDATA("[Measures].[Sum of Hours]",$L$3,"[tbl_MOD].[Shift]","[tbl_MOD].[Shift].&[Day]","[tbl_MOD].[Name]","[tbl_MOD].[Name].&["&trim(L14)"]")

and other variants but with no success.

 

[RoryA]

You appear to be only missing an ampersand there:

=GETPIVOTDATA("[Measures].[Sum of Hours]",$L$3,"[tbl_MOD].[Shift]","[tbl_MOD].[Shift].&[Day]","[tbl_MOD].[Name]","[tbl_MOD].[Name].&["&trim(L14)&"]")

 

[drdrae]

great - works fine, must have missed that small but important detail.

much appreciated