ABS formula not working on pivot?

[helenmeyer]

Hi,

I've got a spreadsheet that i've done an ABS sum on but when I copy it down it doesn't give the correct answer? Half of the spreadsheet is a pivot and the other half isn't? But when I enter the formula separately in each cell it returns the correct answer?

 

[RoryA]

The last argument in your GETPIVOTDATA formula is static, so you are using the first row's value in all the other rows. Replace it with ""&$A2 and the formula should work.

 

[helenmeyer]

Hi and thanks for the quick reply but I still can't get it to work

 

[RoryA]

What is your GETPIVOTDATA formula now?

 

[helenmeyer]

I changed it to what you said and keep getting an error message

 

[RoryA]

That's not what I said. I said the last argument needs to be changed:

=ABS(F5-GETPIVOTDATA("Sum of Quantity",$A$4,"Cost Object",""&$A5))

 

[helenmeyer]

Thank you - that worked a treat. Sorry for getting it wrong

 

[RoryA]

Not a problem. It's quite a strange function in many ways!