| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getpivotdata - able to reference to another cell all parameters but not the datafield
Hi
GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,item2,...) In the above format of the GetPivotData I am able to reference all the Item and Field names but not the Data_Field Example Data_field = "Amount" in Cell A3 field1 = "Income" in Cell A2 Item1 = "Account Type" in Cell A1 The Following Works =getpivotdata("Amount",CashFlow!A7,A1,A2) But When I try and use a reference for the data_Field Then I get a #REF! =getpivotdata(A1,CashFlow!A7,A1,A2) I tried using INDIRECT() , Enetering "Amount" in cell A1 with the " Can anyone help. Thanks Roger |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getpivotdata - able to reference to another cell all parameters but not the datafield
Hi Roger
Field A1 repeated twice instead of A3?? -- Regards Roger Govier "Roger The Rabbit" wrote in message oups.com... Hi GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,item2,...) In the above format of the GetPivotData I am able to reference all the Item and Field names but not the Data_Field Example Data_field = "Amount" in Cell A3 field1 = "Income" in Cell A2 Item1 = "Account Type" in Cell A1 The Following Works =getpivotdata("Amount",CashFlow!A7,A1,A2) But When I try and use a reference for the data_Field Then I get a #REF! =getpivotdata(A1,CashFlow!A7,A1,A2) I tried using INDIRECT() , Enetering "Amount" in cell A1 with the " Can anyone help. Thanks Roger |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getpivotdata - able to reference to another cell all parameters but not the datafield
Hi Roger Govier
No its not repeated , it just does not get data and comes up with #Ref! So if data_field is enetered into the formula as the data like "Amount" it gets the total requested, but when "Amount" in the Formula is replaced by a reference to another cell that does contain the exact same text "Amount" the formula return #Ref! Regards Roger Ramseier Roger Govier wrote: Hi Roger Field A1 repeated twice instead of A3?? -- Regards Roger Govier "Roger The Rabbit" wrote in message oups.com... Hi GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,item2,...) In the above format of the GetPivotData I am able to reference all the Item and Field names but not the Data_Field Example Data_field = "Amount" in Cell A3 field1 = "Income" in Cell A2 Item1 = "Account Type" in Cell A1 The Following Works =getpivotdata("Amount",CashFlow!A7,A1,A2) But When I try and use a reference for the data_Field Then I get a #REF! =getpivotdata(A1,CashFlow!A7,A1,A2) I tried using INDIRECT() , Enetering "Amount" in cell A1 with the " Can anyone help. Thanks Roger |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getpivotdata - able to reference to another cell all parameters but not the datafield
Hi Roger
You say A3 contains the word "Amount", and yet when you tried to substitute the word "Amount" in the Getpivotdata function, you used A1 =getpivotdata("Amount",CashFlow!A7,A1,A2) =getpivotdata(A1,CashFlow!A7,A1,A2) try =getpivotdata(A3,CashFlow!A7,A1,A2) -- Regards Roger Govier "Roger The Rabbit" wrote in message ups.com... Hi Roger Govier No its not repeated , it just does not get data and comes up with #Ref! So if data_field is enetered into the formula as the data like "Amount" it gets the total requested, but when "Amount" in the Formula is replaced by a reference to another cell that does contain the exact same text "Amount" the formula return #Ref! Regards Roger Ramseier Roger Govier wrote: Hi Roger Field A1 repeated twice instead of A3?? -- Regards Roger Govier "Roger The Rabbit" wrote in message oups.com... Hi GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,item2,...) In the above format of the GetPivotData I am able to reference all the Item and Field names but not the Data_Field Example Data_field = "Amount" in Cell A3 field1 = "Income" in Cell A2 Item1 = "Account Type" in Cell A1 The Following Works =getpivotdata("Amount",CashFlow!A7,A1,A2) But When I try and use a reference for the data_Field Then I get a #REF! =getpivotdata(A1,CashFlow!A7,A1,A2) I tried using INDIRECT() , Enetering "Amount" in cell A1 with the " Can anyone help. Thanks Roger |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getpivotdata - able to reference to another cell all parameters but not the datafield
Hi Roger Govier
Yes I did. But looking around other queries I have solved the Problem, by using the T() function it converts the name "Amount" correctly in some way so by doing the following it worked =getpivotdata(T(A1),CashFlow!A7,A1,A2) Why the A1 and A2 reference did not require it and only the datafield required to be converted to text using the T() functio I dont know, the main thing is that it does. Thanks Roger Ramseier Roger Govier wrote: Hi Roger You say A3 contains the word "Amount", and yet when you tried to substitute the word "Amount" in the Getpivotdata function, you used A1 =getpivotdata("Amount",CashFlow!A7,A1,A2) =getpivotdata(A1,CashFlow!A7,A1,A2) try =getpivotdata(A3,CashFlow!A7,A1,A2) -- Regards Roger Govier "Roger The Rabbit" wrote in message ups.com... Hi Roger Govier No its not repeated , it just does not get data and comes up with #Ref! So if data_field is enetered into the formula as the data like "Amount" it gets the total requested, but when "Amount" in the Formula is replaced by a reference to another cell that does contain the exact same text "Amount" the formula return #Ref! Regards Roger Ramseier Roger Govier wrote: Hi Roger Field A1 repeated twice instead of A3?? -- Regards Roger Govier "Roger The Rabbit" wrote in message oups.com... Hi GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,item2,...) In the above format of the GetPivotData I am able to reference all the Item and Field names but not the Data_Field Example Data_field = "Amount" in Cell A3 field1 = "Income" in Cell A2 Item1 = "Account Type" in Cell A1 The Following Works =getpivotdata("Amount",CashFlow!A7,A1,A2) But When I try and use a reference for the data_Field Then I get a #REF! =getpivotdata(A1,CashFlow!A7,A1,A2) I tried using INDIRECT() , Enetering "Amount" in cell A1 with the " Can anyone help. Thanks Roger |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getpivotdata - able to reference to another cell all parameters but not the datafield
Hi Roger
I'm glad you have got it working. However, I am still curious to know how with "Account Type" entered in cell A1 T(A1) can return "Amount" -- Regards Roger Govier "Roger The Rabbit" wrote in message oups.com... Hi Roger Govier Yes I did. But looking around other queries I have solved the Problem, by using the T() function it converts the name "Amount" correctly in some way so by doing the following it worked =getpivotdata(T(A1),CashFlow!A7,A1,A2) Why the A1 and A2 reference did not require it and only the datafield required to be converted to text using the T() functio I dont know, the main thing is that it does. Thanks Roger Ramseier Roger Govier wrote: Hi Roger You say A3 contains the word "Amount", and yet when you tried to substitute the word "Amount" in the Getpivotdata function, you used A1 =getpivotdata("Amount",CashFlow!A7,A1,A2) =getpivotdata(A1,CashFlow!A7,A1,A2) try =getpivotdata(A3,CashFlow!A7,A1,A2) -- Regards Roger Govier "Roger The Rabbit" wrote in message ups.com... Hi Roger Govier No its not repeated , it just does not get data and comes up with #Ref! So if data_field is enetered into the formula as the data like "Amount" it gets the total requested, but when "Amount" in the Formula is replaced by a reference to another cell that does contain the exact same text "Amount" the formula return #Ref! Regards Roger Ramseier Roger Govier wrote: Hi Roger Field A1 repeated twice instead of A3?? -- Regards Roger Govier "Roger The Rabbit" wrote in message oups.com... Hi GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,item2,...) In the above format of the GetPivotData I am able to reference all the Item and Field names but not the Data_Field Example Data_field = "Amount" in Cell A3 field1 = "Income" in Cell A2 Item1 = "Account Type" in Cell A1 The Following Works =getpivotdata("Amount",CashFlow!A7,A1,A2) But When I try and use a reference for the data_Field Then I get a #REF! =getpivotdata(A1,CashFlow!A7,A1,A2) I tried using INDIRECT() , Enetering "Amount" in cell A1 with the " Can anyone help. Thanks Roger |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getpivotdata - able to reference to another cell all parametersbut not the datafield
I'll bet that Roger R. meant to type:
=getpivotdata(T(A3),CashFlow!A7,A1,A2) I usually concatenate an empty string to the reference, e.g.: =getpivotdata(A1 & "",CashFlow!A7,A1,A2) but I don't know why either method is required for the data_field to use a reference. Roger Govier wrote: Hi Roger I'm glad you have got it working. However, I am still curious to know how with "Account Type" entered in cell A1 T(A1) can return "Amount" -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
| Reference Cell Color From Other WorkSheets | Excel Worksheet Functions | |||
| Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Change GETPIVOTDATA cell reference to A1 style | Excel Worksheet Functions | |||
Linear Mode
