Getpivotdata & vlookup

[lostexcelnewbie]

Hello All,

I am trying to pull data from a pivot table, but I want one of the criteria in the formula to change based upon another column in the same row as the formula.

What I have so far is this.

=GETPIVOTDATA("AMOUNT",PriorActualLookup!$A$4,"Project Exp Month","January","PROJ #","7356")

What I want to change is the "7356" to change based upon what is listed in column A2 so that I can copy and paste the formula down. Any ideas ?

 

[Jerry Sullivan]

Welcome to MrExcel,

You've probably already tried substituting "7356" with a reference to A2..
....,"January","PROJ #", A2)

Notice that "7356" is in quotes, which means that it is a Text value instead of a Numeric value.

Because of that, if the value in A2 is the Text value "7356" then substituting "7356" with a reference to A2 would work as you intended.

Since you are posting the question, the value in A2 might be the Numeric value 7356 which would cause the formula to return a #REF error.

You could convert the value in A2 to Text or you could modify your formula to covert a Numeric argument to Text like this...

=GETPIVOTDATA("AMOUNT",PriorActualLookup!$A$4,"Project Exp Month","January","PROJ #", A2&"")

 

[lostexcelnewbie]

That was exactly what I was trying to do, Thank you ! I was just missing the & sign to make it work !