Hi,
Using Excel 2010, Im trying to generate a formula to calculate a running subtotal from a data in a pivot table.
I have a pivot that looks something like this:
<TBODY>
</TBODY>
In cell A8 I have text that determines until what month the data in the report should include (Ex. "May").
What I want is a formula that gives me an accumulated sum of the days, from Jan until whatever month A8 specifies (in this case "May").
Expected result:
A8 = "May" ; Result = 10
A8 = "Feb" ; Result = 4
The catch is that I need a formula that won't need to be reajusted if the location of the data changes. In other words, when Aug is added and the pivot refreshed, the data currently stored in I3 will move to J3. This scenario renders "normal" reference formulas (VLOOKUP, INDEX(MATCH, SUM) useless since cell references might change when the pivot is refreshed. I can't change the ranges every time it is needed because the file will be sent to someone else who wont know how the model is built and we are trying to eliminate all manual input. Just refreshing of the pivots and report generation.
Ideally, I would need some combination of INDEX( with GETPIVOTDATA( to be able to do a =SUM(INDEX(GETPIVOTDATA("Days",$A$1,"Month","Jan","Class","A")):INDEX(GETPIVOTDATA("Days",$A$1,"Month",A8,"Class","A"))). Obviously, Ive tried this and it doesnt work.
As you can see, what would solve this is a formula that returns the reference for the cell that contains the data in a pivot table that matches certain parameters.
Let me know if something is unclear and I'll try to clarify. Your help is very much appreciated!!!!!!
Roberto
Using Excel 2010, Im trying to generate a formula to calculate a running subtotal from a data in a pivot table.
I have a pivot that looks something like this:
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
1 | Column Labels | ||||||||||||||
Hours | Days | ||||||||||||||
2 | Row Labels | Jan | Feb | Mar | Apr | May | Jun | Jul | Jan | Feb | Mar | Apr | May | Jun | Jul |
3 | A | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
4 | B | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
5 | C | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
6 | Total | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 9 | 9 | 9 | 9 | 9 | 9 | 9 |
<TBODY>
</TBODY>
In cell A8 I have text that determines until what month the data in the report should include (Ex. "May").
What I want is a formula that gives me an accumulated sum of the days, from Jan until whatever month A8 specifies (in this case "May").
Expected result:
A8 = "May" ; Result = 10
A8 = "Feb" ; Result = 4
The catch is that I need a formula that won't need to be reajusted if the location of the data changes. In other words, when Aug is added and the pivot refreshed, the data currently stored in I3 will move to J3. This scenario renders "normal" reference formulas (VLOOKUP, INDEX(MATCH, SUM) useless since cell references might change when the pivot is refreshed. I can't change the ranges every time it is needed because the file will be sent to someone else who wont know how the model is built and we are trying to eliminate all manual input. Just refreshing of the pivots and report generation.
Ideally, I would need some combination of INDEX( with GETPIVOTDATA( to be able to do a =SUM(INDEX(GETPIVOTDATA("Days",$A$1,"Month","Jan","Class","A")):INDEX(GETPIVOTDATA("Days",$A$1,"Month",A8,"Class","A"))). Obviously, Ive tried this and it doesnt work.
As you can see, what would solve this is a formula that returns the reference for the cell that contains the data in a pivot table that matches certain parameters.
Let me know if something is unclear and I'll try to clarify. Your help is very much appreciated!!!!!!
Roberto