getpivotdata function and dates


M

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
 
Ad

Advertisements

D

Debra Dalgleish

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

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

Mike said:
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
 
Ad

Advertisements

M

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

Mike said:
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

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top