Cell reference in a pivot table

robertash

New Member
Joined
May 13, 2014
Messages
5
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:

ABCDEFGHIJKLMNO

1
Column Labels
HoursDays
2Row LabelsJanFebMarAprMayJunJulJanFebMarAprMayJunJul
3A11111112222222
4B22222223333333
5C33333334444444
6Total66666669999999

<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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi Roberto,

Are the values of the headers in Row 2 just Text strings "Jan", "Feb",... or are they dates like 1/1/14 that have been formatted as "mmm", or the result of applying grouping?

Similar question for the value in A8: Is it just a string like "May" or a date formatted as "mmm"?

On what row do the labels Hours and Dates fall? In you screen shot, they are shown between rows 1 and 2.

In what cells would the desired formulas be placed that summarize Class A,B,C?
 
Upvote 0
Hi Roberto,

Are the values of the headers in Row 2 just Text strings "Jan", "Feb",... or are they dates like 1/1/14 that have been formatted as "mmm", or the result of applying grouping?

Similar question for the value in A8: Is it just a string like "May" or a date formatted as "mmm"?

On what row do the labels Hours and Dates fall? In you screen shot, they are shown between rows 1 and 2.

In what cells would the desired formulas be placed that summarize Class A,B,C?


Hi Jerry,

Really sorry for such a late reply.

They are all text strings. The values in Row 2 are text strings in that come from a field in a pivot table. The value in A8 is a user input text string.

Sorry about the misunderstanding with the position of the labels. I tried to replicate a pivot table in a table but I dont know how to past with format in this forum and had to do it by hand. I made a mistake and skipped one row. The labels should be placed in row 2 and all other rows numbered consecutively after that. Like this:

ABCDEFGHIJKLMNO
1Column Labels
2HoursDays
3Row LabelsJanFebMarAprMayJunJulJanFebMarAprMayJunJul
4A11111112222222
5B22222223333333
6C33333334444444
7Total66666669999999

<TBODY>
</TBODY>

That renumbering would move the cell in A8 to A9, with this cell containing a text string for the last desired month to be taken into account in the summary cells ("May" for example).

As for the position of the summary formulas, let's place them in A11, A12 & A13 (A, B & C respectively). Please bear in mind that the formula should accomodate for new months being added to the pivot table (for example, new data coming in for both hours and days in Aug which would be placed in column I and and move all "days" data one column to the right) but the formula should continue to work without any update required.

Hope this clarifies things.

Roberto
 
Upvote 0
Why not add all month is the file and leave the values blanc.

The you can make a pivot table, and only refresh the data, to get the result you're lookin for.
 
Upvote 0
Why not add all month is the file and leave the values blanc.

The you can make a pivot table, and only refresh the data, to get the result you're lookin for.

I am already using a pivot table which takes the data from an external database which I don't have control over. So I cannot add blank data to it. Data is added to the database at it becomes available.
 
Upvote 0
Roberto, Try this formula in A11, then copy down.

Code:
=SUM(INDEX($4:$6,ROWS($A$11:$A11),MATCH("Days",$2:$2,0)):
    INDEX($4:$6,ROWS($A$11:$A11),MATCH("Days",$2:$2,0)+
        MATCH($A$9,$3:$3,0)-MATCH("Jan",$3:$3,0)))
 
Upvote 0
You can use GETPIVOTDATA something like this:

=SUM(IFERROR(GETPIVOTDATA("Sum of Days",$A$1,"Row field name",""&A4,"Month",TEXT(DATE(2000,ROW(INDIRECT("1:"&MATCH(A9,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0))),1),"mmm")),0))

which needs array entry.
 
Upvote 0
You can use GETPIVOTDATA something like this:

=SUM(IFERROR(GETPIVOTDATA("Sum of Days",$A$1,"Row field name",""&A4,"Month",TEXT(DATE(2000,ROW(INDIRECT("1:"&MATCH(A9,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0))),1),"mmm")),0))

which needs array entry.

Very nice Rory. I hadn't seen a GetPivotData function used in an Array formula to reference a range of cells.

Could that be shortened to...(also array-entered with Ctrl-Shift-Enter)

=SUM(IFERROR(GETPIVOTDATA("Sum of Days",$A$1,"Unit",""&A4,"Month",TEXT(DATE(2000,ROW(INDIRECT("1:"& MONTH(DATEVALUE("1-"&$A$9&"-2000")))),1),"mmm")),0))
 
Last edited:
Upvote 0
Probably. I did in fact start with that but without knowing the regional settings of the OP, I'm always a bit wary of what will work with DATEVALUE, so I went the long way round. :)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top