Possible formula or VBA solution for cells outside Pivot Table range referencing data to ignore Tier 1 and Tier 2 Row Header?

[gtg007q]

Shown data is a simplified example of a spreadsheet I'm trying to enhance at work, basically I already have a Pivot Table that automatically pulls data from another data source on daily basis which can change with new items being added or removed due to sales.

To the right of this Pivot Table I have bunch of data that references the data shown in Pivot Table that's from another source to come up with bunch of other calculatios and data, and it's where personel keys in notes, confirmed quantity, etc which are manual fields.

Issue I have is that on these daily updates, the row ranges can move due to the reasons described earlier. While I want people to be able to see the sub totals, I don't want my formulas picking up the values of the sub total at Tier 1 (or Tier 2) because I have other tables that may double count the data.

How I do ensure that whatever formula (or VBA) I use will know to ignore Tier 1 (and 2) row headers and only pick up data if the cell being referenced is at the lowest tier of data?


[TABLE="width: 251"]
<colgroup><col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="64" style="width: 48pt;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <tbody>[TR]
[TD="width: 100"]Row Labels
[/TD]
[TD="width: 75"]Sum of Qty[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 96, bgcolor: transparent"]External Table Data Needed
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A
[/TD]
[TD="bgcolor: transparent, align: right"]297[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123[/TD]
[TD="bgcolor: transparent, align: right"]172[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent, align: right"]95[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]95[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]456[/TD]
[TD="bgcolor: transparent, align: right"]114[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Midwest[/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent, align: right"]85[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]85[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]789[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Intl[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]307[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]QQQ[/TD]
[TD="bgcolor: transparent, align: right"]97[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent, align: right"]95[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]95[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ[/TD]
[TD="bgcolor: transparent, align: right"]210[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Midwest[/TD]
[TD="bgcolor: transparent, align: right"]92[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]92[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent, align: right"]53[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]53[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent, align: right"]65[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]65[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2019[/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Intl[/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

Check out the GETPIVOTDATA function for cell formulas

=GETPIVOTDATA("Sum of Qty",A3,"Zone","East")

A3 is a reference to a cell in the PivotTable