sheepshagarmy
New Member
- Joined
- Jan 4, 2015
- Messages
- 12
Hi all,
I'm looking to return data from a pivot table in another tab
(e.g.)
<tbody>
</tbody>
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
I'm looking to return data from a pivot table in another tab
(e.g.)
18/06/2017 | 25/06/2017 | |
Apples | ||
- Red | 32 | 88 |
- Green | 42 | 12 |
Oranges | ||
- Satsumas | 55 | 4 |
- Blood Orange | 50 | 47 |
<tbody>
</tbody>
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