=GETPIVOTDATA dyanmic formula based on cells value

[Bench]

Hi,

I'm trying to get a =GETPIVOTDATA formula to update daily based upon todays date but am struggling.

The data feeds from a SQL DataMart and the formula is:

=GETPIVOTDATA("[Measures].[Case Completed -Invoiced - Count]",SBVSDATA!$A$6,"[Time].[Day]","[Time].[Day].&[25]","[Lender].[MI Grouping]","[Lender].[MI Grouping].&[Cambridge]")

This is the specific part of it i've been trying to play with, replace the [25] with a cell reference A1 for arguments sake that contains TODAY()-1

="[Time].[Day]","[Time].[Day].&[25]"]")

Any ideas, would be so useful to have this running.

Thanks

 

[Andrew Poulsom]

I can't test but try:

INDIRECT("[Time].[Day].&["&A1&"]")

 

[Bench]

Thanks, I've tried

=GETPIVOTDATA("[Measures].[Case Completed -Invoiced - Count]",SBVSDATA!$A$6,"[Time].[Day]",INDIRECT("[Time].[Day].&["&A1&"]"),"[Lender].[MI Grouping]","[Lender].[MI Grouping].&[Cambridge]")

But to no avail, still get a #ref...any other ideas

 

[Andrew Poulsom]

Maybe it's just:

"[Time].[Day].&["&A1&"]"

It's only a string.

 

[Bench]

Maybe it's just:

"[Time].[Day].&["&A1&"]"

It's only a string.

Perfect!! thank you so much, for anyone else coming accross this issue, here is my final formula...Variables!B11 being a cell which has Day(A1) in it A1 being TODAY()-1

=GETPIVOTDATA("[Measures].[Case Completed -Invoiced - Count]",$A$6,"[Time].[Day]","[Time].[Day].&["&Variables!B11&"]","[Lender].[MI Grouping]","[Lender].[MI Grouping].&[Cambridge]")

 

[Bench]

Hi this has been a saviour to me, however i'm now drawing from a different data source which gives a different syntax ie.

GETPIVOTDATA( Data_field, Pivot_table, [Field1], [Item1], [Field2], [Item2], ...)

I'm now struggling to get this to work...

This is what i have so far

=GETPIVOTDATA(" Jobs",$A$5,"Day",Variables!B11)

its the Variables!B11 i can't get to work, the value of which is =DAY(Today())

Any help would be appreciated

Thanks

Ben

 

[Bench]

Almost there, i can make it work if i refer to a cell on the same sheet by using the following

=GETPIVOTDATA(" Jobs",$A$5,"Day",B11&"")

Can't get it work when referring to another sheet tho, the obvious variables!B11&"" didn't work.

Any ideas?

 

[Andrew Poulsom]

With this pivot table:

<TABLE style="WIDTH: 88pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=118 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 width=83>Sum of Jobs</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23 width=35> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>Day</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23 align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=17 align=right x:num>24</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=17 align=right x:num>25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl27 height=17>Grand Total</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num>60</TD></TR></TBODY></TABLE>

This formula worked for me:

=GETPIVOTDATA(" Jobs",$A$5,"Day",Variables!B11)

If Variables!B11 contained 24 the formula returned 20.