Pivot table - #REF issue

Copper Contributor


I wish to build a pivot table to summarize accounting data (GL detail activity) into summary form.  My data is a Table and it contains a variety of GL accounts and amounts.  It is a simple summary of that data.  From there, I would like to link the results from the Pivot into another summary tab - which I am using "get pivot data" formula.  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.  Some months, an underlying GL account disappears, which causes the final result on the Summary tab to reflect the dreaded #REF error.  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.  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!


1 Reply


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

    CUBESETCOUNT($E$9), 2,
            account, CUBERANKEDMEMBER(
                c = 1,
                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 (c...

for more details.