Total two pivot tables into one

[Os]

I have two boxes on a Form "Access 97" that store data
for "picker" and "loader" respectively. When I run my
Excel pivot table report that pulls data from Access, I
was able to show percentage for cases picked in one
report and percentage for cases loaded in another pivot
table report. But to measure employees performance we
have to combine the two percentages "manually" to get the
final number! Is there a way to combine both numbers for
the same individual in one report.

Each employee can be a picker or loader at any given
time. Currently when I run the "picker" report I will
show any picker and all those who loaded with him/her.
Same goes when I run the "loader" report. But I'm having
a little of a problem combining both together.

Any help is highly appreciated.

Thanks.

 

[Debra Dalgleish]

You may be able to use the GETPIVOTDATA function to extract the data you
need. For example:

=GETPIVOTDATA(Pivot1!$A$4,"Joe")+
GETPIVOTDATA(Pivot2!$A$4,"Joe")

Look in Excel's help for information and examples.

 

[Os]

I will try that.

Thanks.

-----Original Message-----
You may be able to use the GETPIVOTDATA function to extract the data you
need. For example:

=GETPIVOTDATA(Pivot1!$A$4,"Joe")+
GETPIVOTDATA(Pivot2!$A$4,"Joe")

Look in Excel's help for information and examples.



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

.

 

[Os]

Yes it works, but I get an error: "#REF!" when an
employee doesn't have data on one of the two tables. I
need to add a function to the formula like" If(ISERROR "
so as when an employee has data on only one table, I
needed that formula to bring his/her score from the other
pivot table and added as his/her total, instead of
showing "#REF!" on the cell. How can I do that?

Thanks.

 

[Debra Dalgleish]

Wrap each of the GETPIVOTDATA formulas in an IF. For example:

IF(ISERROR(GETPIVOTDATA(Pivot1!$A$4,"Joe")),0,GETPIVOTDATA(Pivot1!$A$4,"Joe"))+

IF(ISERROR(GETPIVOTDATA(Pivot2!$A$4,"Joe")),0,GETPIVOTDATA(Pivot12$A$4,"Joe"))

 

[Os]

Many thanks.

-----Original Message-----
Wrap each of the GETPIVOTDATA formulas in an IF. For example:
$A$4,"Joe")),0,GETPIVOTDATA(Pivot1!$A$4,"Joe"))+
$A$4,"Joe")),0,GETPIVOTDATA(Pivot12$A$4,"Joe"))



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

.