Fix GETPIVOTDATA #REF! with Dynamic Item in WPS Spreadsheets Data Model PivotTable
Question details
Fix a #REF! error in GETPIVOTDATA when using a cell reference as the dynamic item argument for a Data Model PivotTable.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a GETPIVOTDATA formula that references a Data Model PivotTable and wants to make the item argument dynamic by using a worksheet cell value instead of hard-coding the member.
- Observed behavior
- GETPIVOTDATA returns a #REF! error when the cell reference is used directly as the item argument in Data Model member syntax.
Identify the Data Model PivotTable, the measure you are retrieving, and the worksheet cell that holds the dynamic item value. Confirm that the requested item is currently displayed in the PivotTable when using GETPIVOTDATA.
Concatenate a dynamic cell value into the Data Model member reference
Use this solution when GETPIVOTDATA returns #REF! because the item argument is a Data Model member that should change based on a worksheet cell.
GETPIVOTDATA for Data Model members requires the item to be placed inside the member expression, not supplied as a plain cell reference. Join the cell value between the final member brackets with ampersand (&) characters so the formula updates dynamically.
Click the cell where you want the GETPIVOTDATA result and type = to start the formula.
Type =GETPIVOTDATA("[Measures].[Median UT]",$A$3, to point to the measure and the top-left cell of the PivotTable.
Continue with "[BatchTensileData].[Work Order-Batch #]","[BatchTensileData].[Work Order-Batch #].&["&M4&"]") and press Enter.
Change the value in cell M4 and confirm that the formula result updates to the matching displayed Data Model member.
Use CUBEVALUE when the requested Data Model item is not displayed
Use this alternative if GETPIVOTDATA continues to return #REF! because the requested member is collapsed or not displayed in the PivotTable.
Use dynamic GETPIVOTDATA formulas in WPS Spreadsheets PivotTables
Build the Data Model member expression by concatenating a worksheet cell so WPS Spreadsheets returns the correct PivotTable measure without hard-coding the item.
- 1. Open your workbook in WPS Spreadsheets: Open the workbook that contains the Data Model PivotTable and the cell value you want to use dynamically.
- 2. Start the GETPIVOTDATA formula: Click the result cell, type =GETPIVOTDATA(, and select the measure and PivotTable reference from your workbook.
- 3. Build the dynamic member expression: Enter the dimension name and concatenate the cell reference inside the final member brackets, for example "[BatchTensileData].[Work Order-Batch #].&["&M4&"]".
- 4. Press Enter and test with a different cell value: Press Enter to return the value, then change the referenced cell to confirm that the formula updates.
Frequently Asked Questions
Why does GETPIVOTDATA return #REF! when I use a cell reference in a Data Model PivotTable?
Because Data Model member syntax requires the item to be placed inside the member expression. You must concatenate the cell value inside the final member brackets, for example "...&["&M4&"]".
Can GETPIVOTDATA return a value that is not currently displayed in the PivotTable?
No, GETPIVOTDATA retrieves values displayed in the PivotTable. If the requested item is collapsed or not displayed, it can return #REF!. In that case, use CUBEVALUE instead.
What does the M4 cell reference do in the GETPIVOTDATA formula?
M4 supplies the dynamic Data Model member value, such as a Work Order-Batch #. The value in M4 is placed between the final member brackets to form the complete member name.
Does WPS Spreadsheets support GETPIVOTDATA with Data Model PivotTables?
WPS Spreadsheets supports GETPIVOTDATA for PivotTable references. For workbook Data Model connections and cube functions, make sure your WPS Spreadsheets version supports the required Data Model or cube features.
How do I make the member name dynamic if it contains spaces or special characters?
Keep the member name inside the square member brackets and make sure the cell value exactly matches the displayed member name, including spaces, ampersands, or other characters.
