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]

Surely the field name would be in column A? You could search for the row position in column A for whatever field you wanted, and use that to get the page field value, like:
=INDEX(B:B,MATCH("Department",A:A,0))

 

[Dunro]

Thanks for responding.

The above code assumes that the page field isn't moved to the first Row label position (otherwise, it would return the second row label).

Unfortunately, it doesn't seem possible to limit the INDEX() and MATCH() to the top of the pivot data.

=IFERROR("PivotTable filtered to Issue type "&INDEX(INDIRECT("'Pvt - Departments'!B1:B"&ROW('Pvt - Departments'!$A$4)),MATCH("Issue2",INDIRECT("'Pvt - Departments'!A1:A"&ROW('Pvt - Departments'!$A$4)),0)),"")

Unfortunately, adding (or removing) a page field moves the corner of the pivot down (or up) without updating all references to A4 in the spreadsheet. Annoying!

I might break down and use VBA to do this.

 

[Dunro]

Oops. Pasted wrong code.

=IFERROR("Filtered to Department "&INDEX(INDIRECT("'Pvt - Issues'!B1:B"&ROW('Pvt - Issues'!$A$4)),MATCH("Dept/Faculty",INDIRECT("'Pvt - Issues'!A1:A"&ROW('Pvt - Issues'!$A$4)),0)),"")

 

[GlennUK]

I would have thought that you wouldn't want to limit the search at all, so the INDIRECT args would be like this:

INDIRECT("'Pvt - Issues'!B1:B999")

... unless you think that having the search going further than the page fields would cause erroneous results?

 

[Dunro]

Yes, going further than the page fields would cause erroneous results if one of the relevant page fields was moved/pivoted to a row field.

 

[GlennUK]

Yes, I see what you mean. I'll have a think about it this afternoon.

Of course, if anyone else has some bright ideas, that would help. I assume that you want a non-VBA solution only?

 

[Dunro]

Yes, I am looking for a non-VBA solution.

(The VBA solution comes to my head more easily, as I can use the PageFields object, etc. But that means forcing my boss to enable macros, etc.)

 

[GlennUK]

You won't have to force the boss to enable macros if you put the workbook in a trusted location.

 

[Dunro]

This is true, until they decide to move the workbook