| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| add filter to pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| Pivot Table defaults for Excel 2007 | Excel Discussion (Misc queries) | |||
| Excel 2007 Pivot Table | Excel Discussion (Misc queries) | |||
| Pivot Table issue in Excel 2007 | Excel Discussion (Misc queries) | |||
Linear Mode
