getpivotdata function and dates

[Mike Lee]

Hello,
I'm wondering if there is a trick to using the
getpivotdata function when one of the labels is a date.
I have a pivot table with 'rep' as the row label
and 'date' as the column label. I'm trying to pull the
data into another (non-pivot) table using the
getpivotdata function. In this table, the reps names are
listed in column A and the dates are in row 5. If I
enter this:

=GETPIVOTDATA(Pivots!A25,A7)

It returns the grand total for the rep whose name is in
cell A7. However, when I try to add the date modifier to
it, I get the #N/A error. I've tried it in the following
ways:

=GETPIVOTDATA(Pivots!A25,A7 & H5)
=GETPIVOTDATA(Pivots!A25,A7 & " " & H5)
=GETPIVOTDATA(Pivots!A25,A7 & Date[2])
=GETPIVOTDATA(Pivots!A25,A7 & "Date[2]")

And when I try:

=GETPIVOTDATA(Pivots!A25,A7 & " Date[2]")

I get the #REF error.

I've had some of these syntaxes work before when the
second label was a text item instead of a date, so I'm
inclined to believe that dates require some sort of
special treatment, but I couldn't find anything in Help.

If anyone has any insight, or can point out something I'm
not seeing, I'd certainly appreciate it.

Thanks very much for the help.

Mike Lee

 

[Debra Dalgleish]

Try formatting the date to match the column heading, e.g.:

=GETPIVOTDATA(Pivots!A25,A7 & " " & TEXT(H5,"m/d/yy"))

Hello,
I'm wondering if there is a trick to using the
getpivotdata function when one of the labels is a date.
I have a pivot table with 'rep' as the row label
and 'date' as the column label. I'm trying to pull the
data into another (non-pivot) table using the
getpivotdata function. In this table, the reps names are
listed in column A and the dates are in row 5. If I
enter this:

=GETPIVOTDATA(Pivots!A25,A7)

It returns the grand total for the rep whose name is in
cell A7. However, when I try to add the date modifier to
it, I get the #N/A error. I've tried it in the following
ways:

=GETPIVOTDATA(Pivots!A25,A7 & H5)
=GETPIVOTDATA(Pivots!A25,A7 & " " & H5)
=GETPIVOTDATA(Pivots!A25,A7 & Date[2])
=GETPIVOTDATA(Pivots!A25,A7 & "Date[2]")

And when I try:

=GETPIVOTDATA(Pivots!A25,A7 & " Date[2]")

I get the #REF error.

I've had some of these syntaxes work before when the
second label was a text item instead of a date, so I'm
inclined to believe that dates require some sort of
special treatment, but I couldn't find anything in Help.

If anyone has any insight, or can point out something I'm
not seeing, I'd certainly appreciate it.

Thanks very much for the help.

Mike Lee

 

[Mike Lee]

Debra,
That did the trick!

Thanks a million for the help.

Mike

-----Original Message-----
Try formatting the date to match the column heading, e.g.:

=GETPIVOTDATA(Pivots!A25,A7 & " " & TEXT(H5,"m/d/yy"))

Hello,
I'm wondering if there is a trick to using the
getpivotdata function when one of the labels is a date.
I have a pivot table with 'rep' as the row label
and 'date' as the column label. I'm trying to pull the
data into another (non-pivot) table using the
getpivotdata function. In this table, the reps names are
listed in column A and the dates are in row 5. If I
enter this:

=GETPIVOTDATA(Pivots!A25,A7)

It returns the grand total for the rep whose name is in
cell A7. However, when I try to add the date modifier to
it, I get the #N/A error. I've tried it in the following
ways:

=GETPIVOTDATA(Pivots!A25,A7 & H5)
=GETPIVOTDATA(Pivots!A25,A7 & " " & H5)
=GETPIVOTDATA(Pivots!A25,A7 & Date[2])
=GETPIVOTDATA(Pivots!A25,A7 & "Date[2]")

And when I try:

=GETPIVOTDATA(Pivots!A25,A7 & " Date[2]")

I get the #REF error.

I've had some of these syntaxes work before when the
second label was a text item instead of a date, so I'm
inclined to believe that dates require some sort of
special treatment, but I couldn't find anything in Help.

If anyone has any insight, or can point out something I'm
not seeing, I'd certainly appreciate it.

Thanks very much for the help.

Mike Lee

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.