GETPIVOTDATA() when column has not title

[Nerdio]

I have created a Pivot Table that looks as follows;

Values
0 To 3 Months 200
3 to 6 Months 111
6 to 12 Months 121
12 to 18 Months 70
18 to 24 Months 24
Over 24 Months 32


The column with the totals you will note does not have a title. So, how do I use GETPIVOTDATA to extract the data; I have started with something like this;

=GETPIVOTDATA(?????,$B$4,"Values","0 To 3 Months")

which I want to return the value of 200. Where $B$4 is the top left hand corner of the Pivot Table, and the ????? is the bit I don't know.

Can anyone help?

Many thanks in anticipation

[MarvinP]

Hi Nerdio,

Pivot Tables need a column head for each column, AND they need to be unique.

It looks like you need/want to use VLookup instead of Pivots. Can you supply a sample workbook and show what you expect as an answer?

[Nerdio]

This is a Pivot Table produced from PowerPivot. The totals I want to get hold of are all derived using CALCULATE(), and are 'Values' in the Pivot Table. I don't know why it does not have a column heading, and I cannot see how to add one.

I can't attach the spreadsheet, because it is dependent on data that is on a database,so it would not work. The following though is a screen shot;

Capture.PNG

[Izandol]

If you will create manual link to a value cell in this pivot table what is the formula created?

[Nerdio]

I didn't realise that if I made a manual link into a Pivot Table, it would generate the GETPIVOTDATA() command. and here is the answer;

=GETPIVOTDATA("[Measures].[TicketCount0To3]",$B$4)

Which makes perfect sense and works of course.

Thanks