Pivot Table Comparisons in Excel 2007
 

Hi!

I work for a translation company and am trying to identify offices that pay
a higher word rate in USD for a specific language.

I am using 2 pivot tables. Pivot table 1 can be filtered by office. Pivot
table 2 is used as the comparison table and left unfiltered (worldwide rates).

I am currently using a basic formula that checks the language in pivot table
1 and copies the average rate from pivot table 2:

IF(B28="Arabic",GETPIVOTDATA("Average Rate",$N$24,"Target Language","Arabic"),
IF(B28="Bulgarian",GETPIVOTDATA("Average Rate",$N$24,"Target
Language","Bulgarian"),
IF(B28="Catalan",GETPIVOTDATA("Average Rate",$N$24,"Target
Language","Catalan"),
etc. I have over 100 languages...

Would it be possible to replace the language names in the formula above by a
variable that would seach for the same language in pivot table 2 and copy the
result beside pivot table 1?
Are there better ways to compare data between pivot tables?

Thanks a lot!

Pivot Table Comparisons in Excel 2007
 

Hi

Try
GETPIVOTDATA("Average Rate",$N$24,"Target Language",B28&"")

--
Regards

Roger Govier


"whynot0611" wrote in message
...
Hi!

I work for a translation company and am trying to identify offices
that pay
a higher word rate in USD for a specific language.

I am using 2 pivot tables. Pivot table 1 can be filtered by office.
Pivot
table 2 is used as the comparison table and left unfiltered (worldwide
rates).

I am currently using a basic formula that checks the language in pivot
table
1 and copies the average rate from pivot table 2:

IF(B28="Arabic",GETPIVOTDATA("Average Rate",$N$24,"Target
Language","Arabic"),
IF(B28="Bulgarian",GETPIVOTDATA("Average Rate",$N$24,"Target
Language","Bulgarian"),
IF(B28="Catalan",GETPIVOTDATA("Average Rate",$N$24,"Target
Language","Catalan"),
etc. I have over 100 languages...

Would it be possible to replace the language names in the formula
above by a
variable that would seach for the same language in pivot table 2 and
copy the
result beside pivot table 1?
Are there better ways to compare data between pivot tables?

Thanks a lot!

Pivot Table Comparisons in Excel 2007
 

Perfect, thanks a lot!

"Roger Govier" wrote:

Hi

Try
GETPIVOTDATA("Average Rate",$N$24,"Target Language",B28&"")

--
Regards

Roger Govier


"whynot0611" wrote in message
...
Hi!

I work for a translation company and am trying to identify offices
that pay
a higher word rate in USD for a specific language.

I am using 2 pivot tables. Pivot table 1 can be filtered by office.
Pivot
table 2 is used as the comparison table and left unfiltered (worldwide
rates).

I am currently using a basic formula that checks the language in pivot
table
1 and copies the average rate from pivot table 2:

IF(B28="Arabic",GETPIVOTDATA("Average Rate",$N$24,"Target
Language","Arabic"),
IF(B28="Bulgarian",GETPIVOTDATA("Average Rate",$N$24,"Target
Language","Bulgarian"),
IF(B28="Catalan",GETPIVOTDATA("Average Rate",$N$24,"Target
Language","Catalan"),
etc. I have over 100 languages...

Would it be possible to replace the language names in the formula
above by a
variable that would seach for the same language in pivot table 2 and
copy the
result beside pivot table 1?
Are there better ways to compare data between pivot tables?

Thanks a lot!