Referencing PivotTable Page Fields "Absolutely"

[Dunro]

Hi there,

Is there a way to reference the current value for a page field without using a macro-enabled file (i.e. without VBA)?

I'm trying to do dynamic chart titles (by having a cell that references the page field I care about), but I get in trouble if additional page fields are added or removed because the cell reference does not update.

Chart Title fx = 'Total Activity'!$A$1
A1 fx = ="Total Activity - "&IF(B3="(All)","",B3&" Positions - ")&IF(B5="(All)",IF(B4="(All)","All Departments",B4),B5)

B3 is the page filter by Employee Group (Unions, etc)
B4 is page filter by Department
B5 is page filter by Sub-Department

Problem is if another page field is added, the page fields move without the formula updating (B3 becomes B4, B4 becomes B5, etc)

I know I can workaround the issue with VBA, but I'm trying to challenge myself (and save myself from teaching new users to enable macros/set their trust settings for something so trivial as a pivotchart title...)

Thanks for the help!

 

[GlennUK]

Ha ha, yeah, that always happens.