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
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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
Joined
May 13, 2014
Messages
5
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
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,209
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
Joined
May 13, 2014
Messages
5
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
Joined
Mar 18, 2010
Messages
8,787
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
Joined
May 2, 2008
Messages
34,338
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
Joined
Mar 18, 2010
Messages
8,787
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
Joined
May 2, 2008
Messages
34,338
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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. :)
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top