Can I 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?

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.

Can anyone help?

 

[TomPl]

I think this formula would do the job:

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

Tom

 

[winnie123]

Hi,

Unfortunately that did not work. It just put the cell reference into the
formula and returned a REF error.

As the data is comming from a pivot table I need to insert the text
contained within B2 into the last bit of the formula. I am changing the
customer name to another customer.

Any other suggestions?

 

[winnie123]

Sorry,

You were correct. I was looking at a customer which did not have any data in
the pivot table.

Thanks a bunch
Winnie

 

[TomPl]

Sorry, you said B2 and I used B5.

If it works with "SUNDRY EXPORT USD" in the formula and "SUNDRY EXPORT USD"
is in cell B2, then it should work with B2 in the formula. Are you sure you
don't have trailing spaces in cell B2, same number of spaces before USD?
Obviously, the text must be exactly the same so that is where I would be
looking for an error.

I just noticed what appears to be an extra set of brackets.


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

Should be:

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

Or:

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

I hope this helps.

Tom