Using Getpivot in sumifs

[cogswel__cogs]

Hi,
I have daily dashboard that I created for work.
It uses pivottable for this months data, but for a number of reasons then uses sumifs to pull in budget and PY data based on date of month % of budget completed/booked at that period of month.

I am using conditional formatting to replicate the pivot table for example Grand total row is blueish anything below pivottable font is white so it wont show..

It all works very well aside from a few sales reps who might contribute to multiple divisions

When I am using the sumifs I am just adding the (divsional sum if +sales rep sum if), so regardless if the drilldown is open or not it will add the two sumifs (one having a value, and one not).

However there are a few sales rep who might contribute to two divisions, and my sumifs do not know that.
Was hoping to use get pivot or something in sumifs to indicate what division sumif should be using. Is this possible?

 

[cogswel__cogs]

Tot attempt to further explain if I turn on get pivot and then click on the sales rep when the pivot able is dropdown is expanded excel creates the following formula.

=GETPIVOTDATA(" Charge Amount",$B$4,"Rep Number","5486","Div","TMT")

I am trying write a sumifs that knows "Div","TMT" for the cell. So I could use both the cells value(rep number) and it Div by in the sumifs

I could change to classic style, and force the reption of teh Div, but was wondering if there was a way to do without redesigning to classic style.

 

[cogswel__cogs]

Guessing this is not possible

 

[Worf]

Maybe we can use VBA to retrieve the needed information, is this solution suitable for you?

 

[cogswel__cogs]

That would be good. Is there a way to capture in vba tell what field a particular cell is report on for a pivottable.

 

[Worf]

The following function retrieves the last formula component. If this is not what you want, please explain.

Function gpd$(cell As Range)
Dim v
v = Split(cell.Formula, ",")
gpd = v(UBound(v))
gpd = Left(gpd, Len(gpd) - 1)
End Function

Plan5

	I
21	43
22	"Y"

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:153px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula I21 =GETPIVOTDATA("Amount",$H$8,"Region","Pacific","Closed","Y") I22 =gpd(I21) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Worf]

Conversely, you can get the pivot table cell’s address, if you know all the pieces to assemble the command:

Sub GetPTCellAddress()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
MsgBox pt.GetPivotData("Amount", "Region", "Pacific", "Closed", "Y").Address
End Sub

 

[cogswel__cogs]

Conversely, you can get the pivot table cell’s address, if you know all the pieces to assemble the command:

Sub GetPTCellAddress()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
MsgBox pt.GetPivotData("Amount", "Region", "Pacific", "Closed", "Y").Address
End Sub

This works incredibly cool and I am already using however it does exact opposite of what I was tryin to do.
Thie will tell you if you are looking Amount for Region = Pacific and "Closed Status" = Y it is in Cell c11

I want to know for C11 what is its region and Closed Status.

 

[Worf]

If the pivot table layout is known, this kind of code should work:

Sub PTable()
Dim pt As PivotTable, desired As Range
Set desired = [j11]                                         ' pivot table cell
Set pt = ActiveSheet.PivotTables(1)
[h15] = Cells(pt.ColumnRange.Rows(2).Row, desired.Column)   ' region
[h16] = Cells(desired.Row, pt.DataLabelRange.Column)        ' status
End Sub

Plan5

	H	I	J	K	L
8	Sum of amount	Column labels
9	row labels	Atlantic	Pacific	Polar	Grand total
10	N	86	5	231	322
11	Y	32	43		75
12	Grand total	118	48	231	397
13
14
15	Pacific
16	Y

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:102px;"><col style="width:128px;"><col style="width:47px;"><col style="width:39px;"><col style="width:76px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4