Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData Multiple Fields
Morning guys.
I have a pivot table which looks like this... COST CENTRE NUMBER VET IT OTHER 7800 750.00 100.00 76.00 In order for me to obtain the sum of the VET and IT cost centre amounts only for the account code 7800 i am currently using the following formula: =GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"Number","7800", "CostCentre","VET") + GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"Number","7800", "CostCentre","IT") So basically i am picking off the two entries from the pivot table seperately and adding them together. Is there any way i can use the getpivotdata function to look up them both and add them together (the formula bar runs out of room if i am using too many accountnumbers and cost centres, so i am looking for a shorter formula) Something like =GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"AccountNumber", "7800","AccountCostCentre","VET" & "AccountCostCentre", "IT") Thanks in advance Bazy2k |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData Multiple Fields
Hi
That can't be done with GetPivotData. You will need to add the various elements together. However, you can cut down the size of the formula by using Index and a named range instead. In my case the PT was on Sheet 4, with the PT in columns A:D. I used Column I to contain the Costcentre required. Adjust the following to suit your layout. InsertNameDefineName Row Refers to =MATCH(Sheet4!$I1,Sheet4!$A:$A,0) In cell J2 enter =IF(ISNA(Row),"",INDEX(B:B,row)+INDEX(C:C,row)) Just keep adding the extra columns as required -- Regards Roger Govier "Bazy2k" wrote in message ... Morning guys. I have a pivot table which looks like this... COST CENTRE NUMBER VET IT OTHER 7800 750.00 100.00 76.00 In order for me to obtain the sum of the VET and IT cost centre amounts only for the account code 7800 i am currently using the following formula: =GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"Number","7800", "CostCentre","VET") + GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"Number","7800", "CostCentre","IT") So basically i am picking off the two entries from the pivot table seperately and adding them together. Is there any way i can use the getpivotdata function to look up them both and add them together (the formula bar runs out of room if i am using too many accountnumbers and cost centres, so i am looking for a shorter formula) Something like =GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"AccountNumber", "7800","AccountCostCentre","VET" & "AccountCostCentre", "IT") Thanks in advance Bazy2k |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData Multiple Fields
Excel 2007 Pivot Table
Several more methods. http://www.mediafire.com/file/twngjmxmoy4/05_14_09.xlsx |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData Multiple Fields
Thanks guys for both your help, i took the easy option in the end, simply
retrieved the data for the two cost centres seperately and then just added them together on my spreadsheet, think i was trying to be too clever about it! Thanks again! "Roger Govier" wrote: Hi That can't be done with GetPivotData. You will need to add the various elements together. However, you can cut down the size of the formula by using Index and a named range instead. In my case the PT was on Sheet 4, with the PT in columns A:D. I used Column I to contain the Costcentre required. Adjust the following to suit your layout. InsertNameDefineName Row Refers to =MATCH(Sheet4!$I1,Sheet4!$A:$A,0) In cell J2 enter =IF(ISNA(Row),"",INDEX(B:B,row)+INDEX(C:C,row)) Just keep adding the extra columns as required -- Regards Roger Govier "Bazy2k" wrote in message ... Morning guys. I have a pivot table which looks like this... COST CENTRE NUMBER VET IT OTHER 7800 750.00 100.00 76.00 In order for me to obtain the sum of the VET and IT cost centre amounts only for the account code 7800 i am currently using the following formula: =GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"Number","7800", "CostCentre","VET") + GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"Number","7800", "CostCentre","IT") So basically i am picking off the two entries from the pivot table seperately and adding them together. Is there any way i can use the getpivotdata function to look up them both and add them together (the formula bar runs out of room if i am using too many accountnumbers and cost centres, so i am looking for a shorter formula) Something like =GETPIVOTDATA("Sum Of GoodsValueInBase",'PivotTab'!$A$3,"AccountNumber", "7800","AccountCostCentre","VET" & "AccountCostCentre", "IT") Thanks in advance Bazy2k |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Fields In Multiple Ranged Pivot Table | Excel Discussion (Misc queries) | |||
Multiple Cell References in GetPivotData | Excel Worksheet Functions | |||
how do I add multiple fields together? | Excel Worksheet Functions | |||
GetPivotData for hidden fields | Excel Discussion (Misc queries) | |||
getpivotdata having multiple items | Excel Discussion (Misc queries) |