Getpivotdata For Related Tables!!

Hello everyone, first time here.

I've been tasked with updating an old reporting spreadsheet so it pulls data from a SSAS model rather than have to be manually updated at the end of each month.

There's 3 important data points, the account number, the period number, and the period balance. I thought the best way to do this without changing the format of the reporting sheet is to create a new page with the SSAS data on a pivot table, then use GetPivotData to update just the period balance cell on the old sheet. The data model consists of a fact_balances table that contains the period balance, and dim_chart_of_accounts that contains the account number, the two tables are related.

When I used the "generate getpivotdata" function, excel gave this formula.
=GETPIVOTDATA("[Measures].[period_balance_sum]",'Model Pivot'!$A$2,"[fact_balances].[period]","[fact_balances].[period].&[1]","[Dim_chart_of_account].[account_no]","[Dim_chart_of_account].[account_no].&[100100010]")
[Measures].[period_balance_sum] is the value GetPivotData is looking for, the period balance of a specific account and month

'Model Pivot'!$A$2 is where my pivot table starts

"[fact_balances].[period]" is field 1, the column on the fact_balances tables that denotes the period

"[fact_balances].[period].&[1]" is item 1. Here's the strange part, rather than just looking up 1 for period 1, like in the getpivotdata tutorials, the table and column is also included

"[Dim_chart_of_account].[account_no]" is field 2, which is the account number.

"[Dim_chart_of_account].[account_no].&[100100010]" is item 2. Just like item 1, it has the table and column name included, rather than just 100100010

What I want to do is instead of having the period and account number hard coded into each getpivotdata, have them reference a column containing the account number. However, simply replacing the items with the cell reference doesn't work. I think it has to do with the way the fields and items are formatted in the formula that excel generated.

Can anyone give some insight into this problem?