| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
GetPivotData does not return total values
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 |
|
#2
|
|||
|
|||
|
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) Anik wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#3
|
|||
|
|||
|
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 ? "Debra Dalgleish" wrote: 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) Anik wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#4
|
|||
|
|||
|
What version of Excel are you using?
Anik wrote: 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 ? "Debra Dalgleish" wrote: 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) Anik wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#5
|
|||
|
|||
|
Excel 2000 with Win2k
"Debra Dalgleish" wrote: What version of Excel are you using? Anik wrote: 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 ? "Debra Dalgleish" wrote: 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) Anik wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#6
|
|||
|
|||
|
Your formula should work. Do you have a problem with the other projects,
or just this one? Anik wrote: Excel 2000 with Win2k "Debra Dalgleish" wrote: What version of Excel are you using? Anik wrote: 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 ? "Debra Dalgleish" wrote: 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) Anik wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#7
|
|||
|
|||
|
Also, try adding single quotes to the formula:
=GETPIVOTDATA($DZ$3,"'" & A2 &"'") Debra Dalgleish wrote: Your formula should work. Do you have a problem with the other projects, or just this one? Anik wrote: Excel 2000 with Win2k "Debra Dalgleish" wrote: What version of Excel are you using? Anik wrote: 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 ? "Debra Dalgleish" wrote: 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) Anik wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#8
|
|||
|
|||
|
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 ! "Debra Dalgleish" wrote: Also, try adding single quotes to the formula: =GETPIVOTDATA($DZ$3,"'" & A2 &"'") Debra Dalgleish wrote: Your formula should work. Do you have a problem with the other projects, or just this one? Anik wrote: Excel 2000 with Win2k "Debra Dalgleish" wrote: What version of Excel are you using? Anik wrote: 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 ? "Debra Dalgleish" wrote: 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) Anik wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#9
|
|||
|
|||
|
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. Anik wrote: 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 ! "Debra Dalgleish" wrote: Also, try adding single quotes to the formula: =GETPIVOTDATA($DZ$3,"'" & A2 &"'") Debra Dalgleish wrote: Your formula should work. Do you have a problem with the other projects, or just this one? Anik wrote: Excel 2000 with Win2k "Debra Dalgleish" wrote: What version of Excel are you using? Anik wrote: 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 ? "Debra Dalgleish" wrote: 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) Anik wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
| Hlookup to return a sum of values | Excel Worksheet Functions | |||
| Index to determine total of values | Excel Worksheet Functions | |||
| Adding multiple cells, return specific values | Excel Worksheet Functions | |||
| How to look up and return multiple values | Excel Worksheet Functions | |||
Linear Mode
