Excel: Using ADDRESS() Formula with GETPIVOTDATA()?

[Armenius]

I am trying to figure out why I cannot use the ADDRESS() formula as the pivot_table variable in the GETPIVOTDATA() formula, and it is driving me absolutely mad. I keep getting a #REF error. Can you simply not use ADDRESS() to reference a Pivot Table? If so I swear this would be the only formula I've come across where this didn't work...

Here is the scenario. Note that this scenario is just my testing conditions. The actual workbook I want to use this in has multiple worksheets for each month with a PivotTable in each of them.

• PivotTable exists in cell A5 of worksheet Sheet2.
• I want to generalize the GETPIVOTDATA() formula to grab values based on a summary table I created.
• Along the top of the summary table is the yyyy-mm period when assignments were due
• In the left column is the staff person's name

Here is the formula I am trying to use:

=GETPIVOTDATA("Due",ADDRESS(5,1,,,"Sheet2"),"Assigned To",$A2,"Due Year-Month",B$1)

Everything works until I replace the pivot_table reference with the ADDRESS() formula...