GETPIVOTDATA #REF for Date

[masplin]

Struggling to convert the fixed date form the GETPIVOTDATA function into a floating date. C22 contains the intial formula for Cell D27 in the pivot table. In D22 I have changed the "Affilaite" tag to a floating ref to cell A22 and works fine. I cannot tell if the pivot table date in D25 is TEXT or a date so I entered 'March 2012 in cell A21 so also text. However changing the GETPIVOTDATA formula from 2012-3-1 T00:00:00 to A21 just shows #REF. I then tried a date in A21 1/3/2012 00:00:00 but no luck.

Can anyone can tell me how to use a floating reference to the date fields i.e. what to put in A21 that works?

Thanks for any advice

Mike









<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td bgcolor="#C0C0C0">
</td> <td align="center" bgcolor="#C0C0C0">A</td><td align="center" bgcolor="#C0C0C0">B</td><td align="center" bgcolor="#C0C0C0">C</td><td align="center" bgcolor="#C0C0C0">D</td><td align="center" bgcolor="#C0C0C0">E</td></tr> <tr><td align="center" bgcolor="#C0C0C0">21</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">March 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td></tr> <tr><td align="center" bgcolor="#C0C0C0">22</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">Affiliate </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&[2012-03-01T00:00:00]","[users].[Code Source]","[users].[Code Source].&[Affiliate]") </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&[2012-03-01T00:00:00]","[users].[Code Source]","[users].[Code Source].&["&A22&"]") </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&["&A21&"]","[users].[Code Source]","[users].[Code Source].&["&A22&"]") </td></tr> <tr><td align="center" bgcolor="#C0C0C0">23</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td></tr> <tr><td align="center" bgcolor="#C0C0C0">24</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">
</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">Reg Month </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td></tr> <tr><td align="center" bgcolor="#C0C0C0">25</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">
</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">April 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">March 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td></tr> <tr><td align="center" bgcolor="#C0C0C0">26</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">code Source </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">Registrations </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">Activated </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">Registrations </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">Activated </td></tr> <tr><td align="center" bgcolor="#C0C0C0">27</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">Affiliate </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">246 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">94 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">76 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">33 </td></tr> <tr><td align="center" bgcolor="#C0C0C0">28</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">Corporate </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">183 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">5 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">92 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">5 </td></tr> </tbody></table>

 

[masplin]

Worked it out. It is text, but in a date looking format so

=TEXT(A20,"yyyy-mm-ddThh:mm:ss")

Where A20 is a date 1/3/2012

 

[StewartS]

Thanks this helped me

 

[jadhava15]

Thanks this helped me

Hi,

Can you please tell me where did you put the Text formula exactly. I am facing the same issue of #ref error.
Thanks in advance.

 

[masplin]

Hi The formula I had was

[COLOR=#000000]=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&["&A21&"]","[users].[Code Source]","[users].[Code Source].&["&A22&"]")[/COLOR]

Where A21 was a cell that looked like it contained a date e.g. 01-Jan-15, but really it was text not a real date (i.e a number)

So I replaced the A21 with TEXT(A21,yyyy-mm-dd hh:mm:ss) so it created a date in the formatt he GETPIVOTDATA formula was expecting i.e. &["&A21&"] became &["&TEXT(A21,yyyy-mm-dd hh:mm:ss)&"]

 

[jadhava15]

Hi The formula I had was

[COLOR=#000000]=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&["&A21&"]","[users].[Code Source]","[users].[Code Source].&["&A22&"]")[/COLOR]

Where A21 was a cell that looked like it contained a date e.g. 01-Jan-15, but really it was text not a real date (i.e a number)

So I replaced the A21 with TEXT(A21,yyyy-mm-dd hh:mm:ss) so it created a date in the formatt he GETPIVOTDATA formula was expecting i.e. &["&A21&"] became &["&TEXT(A21,yyyy-mm-dd hh:mm:ss)&"]

Thanks masplin for quick response.
Got my formula working. I missed [&" thing in formula.