| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I pull field as datafield or as a output from a pivot table re
Hi Friends,
I have Pivot table something like below mentioned : Data CHILD PART NO. FG PART NO. Sum of TOTAL 1010051 ATM0609 11556 1010160 ATM0415 144 PTM0606 2792 PTM0607 3800 1010161 ATM0412 6551 1010162 ATM0415 144 PTM0606 2792 PTM0607 3800 Now, I wish to pull the data "FG PART NO." (not the sum of total) as a result. Is this possible by using "getpivotdata" function. Hope u understood my question. Thanks in advance. Vikram P. Dhemare |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can I pull field as datafield or as a output from a pivot tablere
The GetPivotData function can return the data and totals from the pivot
table, but not the field item text. You can link to a cell that contains a part number, e.g. =B8 Vikram Dhemare wrote: Hi Friends, I have Pivot table something like below mentioned : Data CHILD PART NO. FG PART NO. Sum of TOTAL 1010051 ATM0609 11556 1010160 ATM0415 144 PTM0606 2792 PTM0607 3800 1010161 ATM0412 6551 1010162 ATM0415 144 PTM0606 2792 PTM0607 3800 Now, I wish to pull the data "FG PART NO." (not the sum of total) as a result. Is this possible by using "getpivotdata" function. Hope u understood my question. Thanks in advance. Vikram P. Dhemare -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can I pull field as datafield or as a output from a pivot table re
Thanks Mr. Debra Dalgleish
Is there any option to return the text value? could you give formula with example -- Thanks, Vikram P. Dhemare "Vikram Dhemare" wrote: Hi Friends, I have Pivot table something like below mentioned : Data CHILD PART NO. FG PART NO. Sum of TOTAL 1010051 ATM0609 11556 1010160 ATM0415 144 PTM0606 2792 PTM0607 3800 1010161 ATM0412 6551 1010162 ATM0415 144 PTM0606 2792 PTM0607 3800 Now, I wish to pull the data "FG PART NO." (not the sum of total) as a result. Is this possible by using "getpivotdata" function. Hope u understood my question. Thanks in advance. Vikram P. Dhemare |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can I pull field as datafield or as a output from a pivot tablere
If you provide details on what value you want to return, someone may be
able to help with a formula. Vikram Dhemare wrote: Thanks Mr. Debra Dalgleish Is there any option to return the text value? could you give formula with example -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can I pull field as datafield or as a output from a pivot tabl
Here is the pivot table :
A B C RC1 1010051 ATM0609 11556 RC2 1010160 ATM0415 144 RC3 PTM0606 2792 RC4 PTM0607 3800 RC5 1010161 ATM0412 6551 RC6 1010162 ATM0415 144 RC7 PTM0606 2792 RC8 PTM0607 3800 The result would be: 1010162 =INDEX(A1:C8,MATCH(A10,A1:A8,3),row(2),col.(2)) i.e. the returned field value would be PTM0606 =INDEX(A1:C8,MATCH(A10,A1:A8,3),row(3),col.(2)) i.e. the returned field value would be PTM0607 I have tried this: in Cell B10 =INDEX(A1:C8,MATCH(A10,A1:A8,2),2) in Cell B11 =INDEX(A1:C8,MATCH(A10,A1:A8,3),2) but the value is returning ATM0415 i.e the first corresponding row value of given criteria. Pl. help me out as I am in desparately need the solution for this. -- Thanks, Vikram P. Dhemare "Debra Dalgleish" wrote: If you provide details on what value you want to return, someone may be able to help with a formula. Vikram Dhemare wrote: Thanks Mr. Debra Dalgleish Is there any option to return the text value? could you give formula with example -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can I pull field as datafield or as a output from a pivot tabl
The following formula would return PTM0606 with 1010162 in cell A10:
=OFFSET(A1,MATCH(A10,A1:A8,1),1) Vikram Dhemare wrote: Here is the pivot table : A B C RC1 1010051 ATM0609 11556 RC2 1010160 ATM0415 144 RC3 PTM0606 2792 RC4 PTM0607 3800 RC5 1010161 ATM0412 6551 RC6 1010162 ATM0415 144 RC7 PTM0606 2792 RC8 PTM0607 3800 The result would be: 1010162 =INDEX(A1:C8,MATCH(A10,A1:A8,3),row(2),col.(2)) i.e. the returned field value would be PTM0606 =INDEX(A1:C8,MATCH(A10,A1:A8,3),row(3),col.(2)) i.e. the returned field value would be PTM0607 I have tried this: in Cell B10 =INDEX(A1:C8,MATCH(A10,A1:A8,2),2) in Cell B11 =INDEX(A1:C8,MATCH(A10,A1:A8,3),2) but the value is returning ATM0415 i.e the first corresponding row value of given criteria. Pl. help me out as I am in desparately need the solution for this. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can I pull field as datafield or as a output from a pivot table re
Hi Debra, This is interesting. Pivottables are so quick and easy to create, and so useful, but I've not been able to effectively re-use the data summarised by pivottables into other reports without retyping. I assume that if the source data changed (say another part number appear in the following month, then formulae extracting figures from the pivottable may become invalid. I understand that any figure in pivottables can be drilled down on to give all source entries - which is fantastic - but this is the only report I've been able to effectively reprocess pivottable data into. Is there any source which explains how to re-use and reprocess pivottable data? Any tips & tricks? Thanks, -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531163 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can I pull field as datafield or as a output from a pivot tablere
The sample formula in my previous post will pull a field item from the
pivot table. To pull data, you can use the GetPivotData function, as described in Excel's Help, and he http://www.contextures.com/xlPivot06.html John James wrote: Hi Debra, This is interesting. Pivottables are so quick and easy to create, and so useful, but I've not been able to effectively re-use the data summarised by pivottables into other reports without retyping. I assume that if the source data changed (say another part number appear in the following month, then formulae extracting figures from the pivottable may become invalid. I understand that any figure in pivottables can be drilled down on to give all source entries - which is fantastic - but this is the only report I've been able to effectively reprocess pivottable data into. Is there any source which explains how to re-use and reprocess pivottable data? Any tips & tricks? Thanks, -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can I pull field as datafield or as a output from a pivot table re
Thanks Debra. Much clearer now. -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531163 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting of Pivot Table Field | Excel Discussion (Misc queries) | |||
| How to get pivot table Time field to appear correctly | Excel Worksheet Functions | |||
| Pivot Table Comment Field | Excel Discussion (Misc queries) | |||
| Creating Calculations Within a Pivot Table Field | Excel Discussion (Misc queries) | |||
| pivot table field name error | Excel Worksheet Functions | |||
Linear Mode
