Edit a GETPIVOTDATA formula?

[winnie123]

Hi,

I have a list of customers in Column B and I would like to copy the value in
B2 to replace the end of a formula in Column c

The formula in column c is

=(GETPIVOTDATA("Sum of Qty",'Y:\Spares sales\[2008.xls]2008
Table'!$A$5,"Customer Name","SUNDRY EXPORT USD"))

The last bit of the formula SUNDRY EXPORT USD needs to change to whatever is
in Column B for the same row.

It it possible?

Thanks
Winnie

 

[Barb Reinhardt]

You can replace "SUNDRY EXPORT USD" with B5 (or some other cell address.

 

[winnie123]

Hi,

I tried that and all it does is add the cell reference to the formula not
the value of the cell reference.

I tried to create a macro which looks like this

Range("B2").Select
ActiveCell.FormulaR1C1 = "SUNDRY HOME"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=(GETPIVOTDATA(""Sum of Qty"",'[2008.xls]2008
Table'!R5C1,""Customer Name"",""SUNDRY HOME""))"
Range("D2").Select

But I dont know how to make it work for the rest of Column B.

I am pulling the info from a pivot table and just wanted to save time by
copying and pasting the same formula but need a macro to change the last bit
in the formula ie The Customer name.

I hope I have made it a bit clearer on what i'm trying to achieve.

Thanks for your help.

You can replace "SUNDRY EXPORT USD" with B5 (or some other cell address.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

Hi,

I have a list of customers in Column B and I would like to copy the value in
B2 to replace the end of a formula in Column c

The formula in column c is

=(GETPIVOTDATA("Sum of Qty",'Y:\Spares sales\[2008.xls]2008
Table'!$A$5,"Customer Name","SUNDRY EXPORT USD"))

The last bit of the formula SUNDRY EXPORT USD needs to change to whatever is
in Column B for the same row.

It it possible?

Thanks
Winnie

 

[Madiya]

Hi,

I tried that and all it does is add the cell reference to the formula not
the value of the cell reference.

I tried to create a macro which looks like this

Range("B2").Select
    ActiveCell.FormulaR1C1 = "SUNDRY HOME"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=(GETPIVOTDATA(""Sum of Qty"",'[2008.xls]2008
Table'!R5C1,""Customer Name"",""SUNDRY HOME""))"
    Range("D2").Select

But I dont know how to make it work for the rest of Column B.

I am pulling the info from a pivot table and just wanted to save time by
copying and pasting the same formula but need a macro to change the last bit
in the formula ie The Customer name.

I hope I have made it a bit clearer on what i'm trying to achieve.

Thanks for your help.

You can replace "SUNDRY EXPORT USD" with B5 (or some other cell address..

If this post was helpful to you, please click YES below.

Hi,
I have a list of customers in Column B and I would like to copy the value in
B2 to replace the end of a formula in Column c
The formula in column c is
=(GETPIVOTDATA("Sum of Qty",'Y:\Spares sales\[2008.xls]2008
Table'!$A$5,"Customer Name","SUNDRY EXPORT USD"))
The last bit of the formula SUNDRY EXPORT USD needs to change to whatever is
in Column B for the same row.
It it possible?
Thanks
Winnie- Hide quoted text -

- Show quoted text -

Try INDIRECT for "SUNDRY EXPORT USD" part along with LEFT.

Regards,
Madiya

 

[winnie123]

Hi Madiya,

I am not sure where to put the Indirect and not sure what you mean by part
along with LEFT.

Sorry I am only just learning.

Thanks
Winnie

Hi,

I tried that and all it does is add the cell reference to the formula not
the value of the cell reference.

I tried to create a macro which looks like this

Range("B2").Select
ActiveCell.FormulaR1C1 = "SUNDRY HOME"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=(GETPIVOTDATA(""Sum of Qty"",'[2008.xls]2008
Table'!R5C1,""Customer Name"",""SUNDRY HOME""))"
Range("D2").Select

But I dont know how to make it work for the rest of Column B.

I am pulling the info from a pivot table and just wanted to save time by
copying and pasting the same formula but need a macro to change the last bit
in the formula ie The Customer name.

I hope I have made it a bit clearer on what i'm trying to achieve.

Thanks for your help.

You can replace "SUNDRY EXPORT USD" with B5 (or some other cell address..

If this post was helpful to you, please click YES below.

:

I have a list of customers in Column B and I would like to copy the value in
B2 to replace the end of a formula in Column c

The formula in column c is

=(GETPIVOTDATA("Sum of Qty",'Y:\Spares sales\[2008.xls]2008
Table'!$A$5,"Customer Name","SUNDRY EXPORT USD"))

The last bit of the formula SUNDRY EXPORT USD needs to change to whatever is
in Column B for the same row.

It it possible?

Thanks
Winnie- Hide quoted text -

- Show quoted text -

Try INDIRECT for "SUNDRY EXPORT USD" part along with LEFT.

Regards,
Madiya

 

[Madiya]

Hi Madiya,

I am not sure where to put the Indirect and not sure what you mean by part
along with LEFT.

Sorry I am only just learning.

Thanks
Winnie

Hi,
I tried that and all it does is add the cell reference to the formulanot
the value of the cell reference.
I tried to create a macro which looks like this
Range("B2").Select
    ActiveCell.FormulaR1C1 = "SUNDRY HOME"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=(GETPIVOTDATA(""Sum of Qty"",'[2008.xls]2008
Table'!R5C1,""Customer Name"",""SUNDRY HOME""))"
    Range("D2").Select
But I dont know how to make it work for the rest of Column B.
I am pulling the info from a pivot table and just wanted to save timeby
copying and pasting the same formula but need a macro to change the last bit
in the formula ie The Customer name.
I hope I have made it a bit clearer on what i'm trying to achieve.
Thanks for your help.
:
You can replace "SUNDRY EXPORT USD" with B5 (or some other cell address..
--
HTH,
Barb Reinhardt
If this post was helpful to you, please click YES below.
:
Hi,
I have a list of customers in Column B and I would like to copy the value in
B2 to replace the end of a formula in Column c
The formula in column c is
=(GETPIVOTDATA("Sum of Qty",'Y:\Spares sales\[2008.xls]2008
Table'!$A$5,"Customer Name","SUNDRY EXPORT USD"))
The last bit of the formula SUNDRY EXPORT USD needs to change to whatever is
in Column B for the same row.
It it possible?
Thanks
Winnie- Hide quoted text -
- Show quoted text -

Try INDIRECT for "SUNDRY EXPORT USD" part along with LEFT.

Regards,
Madiya- Hide quoted text -

- Show quoted text -

You can use INDIRECT function to define a parameter in any formula.
In your case, if "SUNDRY EXPORT USD" is in say cell B5, then you can
use
indirect(B5) instead of "SUNDRY EXPORT USD".
If you want part of "SUNDRY EXPORT USD" say "SUNDRY EXPORT" only
then you can use =LEFT(B5,13) which will give you "SUNDRY EXPORT"

Hope this is usefull to you but I am not sure that it will work for
pivot tables
Since I am not an expert, you may like to refer to excel help for
exact syntext.

Regards.
Madiya