GetPivotData does not return total values

G

Guest

Here is my Pivot Table, starting in cell DZ3:

Sum of Time Project
Employee J001 : Construct Room With Materials J003 : Buy Materials With
Money
Frances, Mario 172.75 136
Peris, Sylvain 9 21.25
Smith, Fred 22.5

A1 = "Peris, Sylvain"
A2 = "J001 : Construct Room"

=GETPIVOTDATA($DZ$3, A1) --> returns total of Sylvain Peris Hours, which is
809.25.
=GETPIVOTDATA($DZ$3, A2) --> should return total of project J001 but I get
the N/A value.

I read all the posts and tried everything. Subtotals of each fields,
projects and people are automatic, and I can read the Grand Total in the last
column (people total hours) and last line (project total hours)

Hope you have a suggestion !
Anik
 
D

Debra Dalgleish

It looks like your project name in the column area is:
J001 : Construct Room With Materials

which doesn't match the entry in cell A2 (J001 : Construct Room)
 
G

Guest

I'm sorry I didn't explain it right. In fact, A2 = "J001 : Construct Room
With Materials" because I'm using copy-paste.
Can someone help me please ?
 
D

Debra Dalgleish

What version of Excel are you using?
I'm sorry I didn't explain it right. In fact, A2 = "J001 : Construct Room
With Materials" because I'm using copy-paste.
Can someone help me please ?

:
Click to expand...
 
D

Debra Dalgleish

Your formula should work. Do you have a problem with the other projects,
or just this one?
 
D

Debra Dalgleish

Also, try adding single quotes to the formula:

=GETPIVOTDATA($DZ$3,"'" & A2 &"'")
 
G

Guest

Thank you very much Debra, it worked that way with the "'" on each side of
the cell content. :)
I still don't get why GETPIVOTDATA would not recognize:
"Peris, Sylvain" but not:
"J001 : Construct Room With Materials"

:|
Thanks a lot !
 
D

Debra Dalgleish

Thanks for letting me know that you got it working.

Sometimes it seems to get confused if there are spaces in the item name,
and other similar items in the pivot table.
 
Top