Pivot table - #REF issue

AdriennePCO 

To update it's dynamically it's better to do on Excel which supports dynamic arrays (365) with help of data model.

For such sample

accounts in H3:H7 are added manually and in next column is standard GETPIVOTDATA.

Now in E9 let get cubeset from data model as

=CUBESET("ThisWorkbookDataModel", "[Table1].[Account].children", "Accounts")

and in H10 add the spill as

=MAKEARRAY(
    CUBESETCOUNT($E$9), 2,
    LAMBDA(r,c,
        LET(
            account, CUBERANKEDMEMBER(
                "ThisWorkbookDataModel",
                "[Table1].[Account].children",
                r
            ),
            IF(
                c = 1,
                account,
                CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total]", account)
            )
        )
    )
)

With that accounts and totals will be shown dynamically. On top it could be added filtering if necessary.

Please check

Cubevalue and spilled range - Microsoft Community Hub

Excel Cube Functions, Dynamic Arrays And Lambdas, Part 2: Returning Tables - Chris Webb's BI Blog (crossjoin.co.uk)

for more details.