Hi @DSM_user,
To fix the issue of GETPIVOTDATA disabled but not working, you can try the following solutions:
Solution 1: Check the file format
- Open the file in Excel.
- Click the File tab.
- Click Info.
- Under File Size and Properties, look for the File Format section.
- If the file format is not Excel Workbook (.xlsx), you can save the file to the Excel Workbook format by clicking Save As and selecting Excel Workbook (.xlsx) from the Save as type dropdown menu.
Solution 2: Check if the PivotTable is based on the Workbook Data Model
- Select the PivotTable.
- Click the PivotTable Analyze tab.
- In the Data group, click the Change Data Source button.
- In the Choose Data Source dialog box, look for the Workbook Data Model section.
- If the PivotTable is based on the Workbook Data Model, the Workbook Data Model option will be selected.
If the PivotTable is based on the Workbook Data Model, you cannot use the GETPIVOTDATA function to retrieve data from it.
Solution 3: Use the VLOOKUP function
If you cannot use the GETPIVOTDATA function, you can use the VLOOKUP function to look up values in a PivotTable and return the corresponding value from another column.
To use the VLOOKUP function to look up a value in a PivotTable, you will need to know the following information:
- The column that contains the value you want to look up (the lookup value column).
- The column that contains the value you want to return (the return value column).
- The table or PivotTable that contains the lookup value column and the return value column.
Once you have this information, you can use the following formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Where:
- lookup_value is the value you want to look up in the table or PivotTable.
- table_array is the table or PivotTable that contains the lookup value column and the return value column.
- col_index_num is the column number of the return value column in the table or PivotTable.
- range_lookup is an optional argument that specifies whether you want to perform an exact match (FALSE) or an approximate match (TRUE).
For example, the following formula would look up the value "Sales" in the PivotTable in A1:B10 and return the corresponding value from column B:
=VLOOKUP("Sales", A1:B10, 2, FALSE)
This formula would return the value 1000, which is the sales value for the month of "Sales".
You can also use the VLOOKUP function to look up multiple values in a PivotTable. To do this, you would use the following formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup], [return_array])
Where:
- lookup_value is the value you want to look up in the table or PivotTable.
- table_array is the table or PivotTable that contains the lookup value column and the return value column.
- col_index_num is the column number of the return value column in the table or PivotTable.
- range_lookup is an optional argument that specifies whether you want to perform an exact match (FALSE) or an approximate match (TRUE).
- return_array is an optional argument that specifies the range of cells that contain the values you want to return.
For example, the following formula would look up the values "Sales" and "Marketing" in the PivotTable in A1:B10 and return the corresponding values from column B:
=VLOOKUP({"Sales", "Marketing"}, A1:B10, 2, FALSE, {"Sales", "Marketing"})
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.
If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic (LinkedIn)
