Total two pivot tables into one

O

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.
 
D

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.
 
O

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.
 
D

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

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

Similar Threads

Combining data from multiple fields into Pivot table 0
Multiple criteria in one query 1
Datepicker is not included in runtime 0
How I can create a Pivot table by selecting two Different Pivot 1
Pivot table report invalid 0
List all tables, including document structure 0
Pivot Table with Data Model Connection - getpivotdata reference issue 0
Getting two fields into one 5

Top