GETPIVOTDATA - datafield

[sheepshagarmy]

Hi all,

I'm looking to return data from a pivot table in another tab

(e.g.)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]18/06/2017[/TD]
[TD]25/06/2017[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] - Red[/TD]
[TD]32[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD] - Green[/TD]
[TD]42[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] - Satsumas[/TD]
[TD]55[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD] - Blood Orange[/TD]
[TD]50[/TD]
[TD]47[/TD]
[/TR]
</tbody>[/TABLE]


If the table above was a simple version of a pivot table - and in another tab I'd like to read how many red apples I have on the 18th of June, that's fine as I have used:

=GETPIVOTDATA("18/06/2017",PIVOT!$B$3,"Fruit","APPLE","Type","RED")

(GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...))

However, the worksheet in question is rolled once a week using a macro - a new sheet is created with the same formulas as the last version while the data rolls. So in the example above, 18/06/2017 would delete and Cell B1 becomes 25/06/2017. I would like the formula above to then return the new value for Cell B1 - and so now giving me the data for Red Apples on 18/06/2017.

Is there any way I can make the data_field match the column in question rather than go and look for the field 18/06/2017 in the pivot table?

Tried my best but cannot do it, help!!

Cheers

SSA

 

[RoryA]

Assuming you have a cell with that value in it, you can use that cell reference - e.g. instead of "18/06/2017", you use ""&B2. If the cell in question contains an actual date value rather than text, you may need to format it with the TEXT function like this:

""&TEXT(B2,"dd/mm/yyyy")

 

[sheepshagarmy]

That's great, worked perfectly, thanks Rory

SSA