I have a very simple pivot table:

Name | Sales | Cost

I'm using the GETPIVOTDATA function to grab data from it.

=GETPIVOTDATA("Sales", $A$3, "Name", "Adam")

So far so good.

Then, in order to make this formula draggable, I replace the name of
the salesperson with a cell reference.

=GETPIVOTDATA("Sales", $A$3, "Name", C14)

Still works.

However, I want to take it one more level and replace the field name
that's outputted ("Sales") with another cell reference. I try it and it
returns a #REF message:

=GETPIVOTDATA(D13, $A$3, "Name", C14)

This isn't a formatting issue either, because cell D13 is spelled and
formatted exactly the same in the pivot as it is outside the pivot.
Also, running an IF= statement turns up true, further proving this.

Is this an Excel glitch? I'm quite baffled...

Try this

=GETPIVOTDATA(D13&"",$A$3,"Name",C14)

(i don't know why either). Maybe some god will let us know.
--
Allllen


" wrote:

I have a very simple pivot table:

Name | Sales | Cost

I'm using the GETPIVOTDATA function to grab data from it.

=GETPIVOTDATA("Sales", $A$3, "Name", "Adam")

So far so good.

Then, in order to make this formula draggable, I replace the name of
the salesperson with a cell reference.

=GETPIVOTDATA("Sales", $A$3, "Name", C14)

Still works.

However, I want to take it one more level and replace the field name
that's outputted ("Sales") with another cell reference. I try it and it
returns a #REF message:

=GETPIVOTDATA(D13, $A$3, "Name", C14)

This isn't a formatting issue either, because cell D13 is spelled and
formatted exactly the same in the pivot as it is outside the pivot.
Also, running an IF= statement turns up true, further proving this.

Is this an Excel glitch? I'm quite baffled...

Hi Allllen

Thanks so much, works like a charm!

Adam

Allllen wrote:
Try this

=GETPIVOTDATA(D13&"",$A$3,"Name",C14)

(i don't know why either). Maybe some god will let us know.
--
Allllen


" wrote:

I have a very simple pivot table:

Name | Sales | Cost

I'm using the GETPIVOTDATA function to grab data from it.

=GETPIVOTDATA("Sales", $A$3, "Name", "Adam")

So far so good.

Then, in order to make this formula draggable, I replace the name of
the salesperson with a cell reference.

=GETPIVOTDATA("Sales", $A$3, "Name", C14)

Still works.

However, I want to take it one more level and replace the field name
that's outputted ("Sales") with another cell reference. I try it and it
returns a #REF message:

=GETPIVOTDATA(D13, $A$3, "Name", C14)

This isn't a formatting issue either, because cell D13 is spelled and
formatted exactly the same in the pivot as it is outside the pivot.
Also, running an IF= statement turns up true, further proving this.

Is this an Excel glitch? I'm quite baffled...