Pivot Table Issues

Fix GETPIVOTDATA #REF Error with Dynamic Measures in Power Pivot

Steven Parker Nov 2, 2026 869 views

Question details

The user needs to dynamically reference measure names using cell references in a Power Pivot report without triggering a #REF error.

How to Fix GETPIVOTDATA #REF Error with Dynamic Measures in Power Pivot

Product: Spreadsheet
Device & OS: not provided
Scenario: Building a flexible financial or general-ledger report that pulls various measures dynamically based on cell references.
Observed behavior: The GETPIVOTDATA function returns a #REF error when its measure argument is dynamically constructed from a cell reference within a Power Pivot report.

Before you start

Ensure your Power Pivot data model is fully loaded and that the exact measure names you plan to reference dynamically actually exist in the background Data Model.

Solution 1Recommended

Replace GETPIVOTDATA with the CUBEVALUE Function

CUBE functions are designed specifically for Data Models and natively support dynamic string concatenation and cell references for measure names, bypassing the limitations of GETPIVOTDATA.

While GETPIVOTDATA is excellent for standard Pivot Tables, it is rigid and often fails when trying to concatenate dynamic strings for measure names inside a Power Pivot Data Model. CUBEVALUE interacts directly with the Data Model rather than the visible Pivot Table, allowing complete flexibility.

Identify your dynamic variables

Locate or create the cell containing the dynamic measure name you want to reference (e.g., cell A1 contains the text 'Total Revenue').

Select the target cell

Click on the cell where you want the calculated value to be displayed.

Enter the CUBEVALUE formula

Type the formula to reference the Data Model and dynamically concatenate your cell reference. For example: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[" & A1 & "]")

Add additional slicer or filter contexts

If you need to filter by a specific general-ledger code dynamically, add another argument linking to that cell, such as: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[" & A1 & "]", "[GL_Codes].[Code].[" & B1 & "]")

Replace GETPIVOTDATA with the CUBEVALUE Function

Formula Flexibility: Using CUBEVALUE ensures your formulas easily handle dynamic general-ledger codes and multiple measures without breaking, even if the visual layout of a Pivot Table changes.

Free Microsoft Office alternative

Handle Pivot Tables and Spreadsheets Seamlessly with WPS Office

While highly advanced Power Pivot Data Models and CUBE functions are specific to Microsoft Excel environments, WPS Office Spreadsheet provides a highly compatible, robust environment for standard Pivot Tables, conventional GETPIVOTDATA operations, and everyday data analysis at no cost.

1. Download and Install WPS Office: Visit the official WPS website, download the free installer, and complete the installation process.
2. Open WPS Spreadsheet: Launch the WPS Office suite and click on 'Spreadsheet' to start a new workspace.
3. Import Your Data: Drag and drop your existing .xlsx files into the application to continue building your standard Pivot Tables and reports.

Highly compatible with standard Microsoft Excel formats (.xlsx and .xls)Robust standard Pivot Table and charting capabilities for data analysisLightweight application with exceptionally fast calculation speedsFree and easy-to-use familiar interface for a seamless transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does GETPIVOTDATA return a #REF error for dynamic measures?

GETPIVOTDATA is structured to read directly from the visible layout of a Pivot Table. If the dynamically constructed text string isn't an exact match to a currently displayed field, or if it queries a Data Model measure in a way the layout doesn't support, it breaks and returns a #REF error.

Can I use the INDIRECT function with GETPIVOTDATA to fix the dynamic reference?

While the INDIRECT function can help build standard cell references dynamically in regular formulas, it often fails when used within the specific measure argument of a GETPIVOTDATA function connected to a Power Pivot Data Model. CUBE functions are the reliable alternative.

How do I find the correct Data Model connection name for CUBEVALUE?

In most modern spreadsheet environments that support this feature, the default local Data Model connection name used inside CUBE functions is simply "ThisWorkbookDataModel".

Excel & Data Problems