# Cell reference in a pivot table

#### robertash

##### New Member
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:

 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

#### Jerry Sullivan

##### MrExcel MVP
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?

#### robertash

##### New Member
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:

 A B C D E F G H I J K L M N O 1 Column Labels 2 Hours Days 3 Row Labels Jan Feb Mar Apr May Jun Jul Jan Feb Mar Apr May Jun Jul 4 A 1 1 1 1 1 1 1 2 2 2 2 2 2 2 5 B 2 2 2 2 2 2 2 3 3 3 3 3 3 3 6 C 3 3 3 3 3 3 3 4 4 4 4 4 4 4 7 Total 6 6 6 6 6 6 6 9 9 9 9 9 9 9

<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

#### Oeldere

##### Well-known Member
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.

#### robertash

##### New Member
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.

#### Jerry Sullivan

##### MrExcel MVP
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)))``````

#### RoryA

##### MrExcel MVP, Moderator
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.

#### Jerry Sullivan

##### MrExcel MVP
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:

#### RoryA

##### MrExcel MVP, Moderator
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.

1,081,994
Messages
5,362,607
Members
400,684
Latest member
Vie

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...