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?

Row Labels	Sum of Qty		External Table Data Needed
A	297
123	172
East	77		77
South	95		95
456	114
Midwest	29		29
West	85		85
789	11
Intl	11		11
B	307
QQQ	97
East	95		95
South	2		2
XYZ	210
Midwest	92		92
South	53		53
West	65		65
C	29
2019	29
Intl	29		29

<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> </tbody>

 

[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