Pivot table - #REF issue

Question

Hi:I wish to build a pivot table to summarize accounting data (GL detail activity) into summary form.&nbsp; My data is a Table and it contains a variety of GL accounts and amounts.&nbsp; It is a simple summary of that data.&nbsp; From there, I would like to link the results from the Pivot into another summary tab - which I am using "get pivot data" formula.&nbsp; All of this is known and easy for me.My problem is that I wish to build this so it can be re-populated each month, refresh the Pivot and update the final summary tab.&nbsp; Some months, an underlying GL account disappears, which causes the final result on the Summary tab to reflect the dreaded #REF error.&nbsp; I'd like to know if anyone knows how to account for that and replace it with zero, so that my summary formulae display correctly.&nbsp; And, they are "ready" to receive data for that missing GL account when it reappears in a subsequent month.Any suggestions would be greatly appreciated!Adrienne

sergeibaklan · Answer

AdriennePCO&nbsp;
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.

Forum Discussion

Pivot table - #REF issue

Share

Resources