Forum Discussion

stevefootie's avatar
stevefootie
Copper Contributor
Jan 15, 2025

Get Pivot Table Data returns zero

I have a consolidated spreadsheet that uses the getpivottable formula linked to another password protected file. however when the source document is not open the formulas return a zero. Is there a better way for the correct values to be shown when opening just the consolidated spreadsheet. Due to the sensitive nature of the original spreadsheet it is not possible for all users to have access to this file. Even when the consolidated sheet has been saved with the correct data showing, when re-opening the document the values change to zero.

  • Try below:

     

    • Data Cache: Enable the option to cache PivotTable data in the connection properties. This way, the PivotTable retains data when opening the consolidated sheet, although the original file remains closed.
    • IFERROR Function: Combine the IFERROR function with GETPIVOTDATA to return an alternate value instead of zero when the source is unavailable.
      =IFERROR(GETPIVOTDATA("YourField",PivotTableCell),YourAlternateValue)
      
    • Consolidated Data Update: Create a macro to refresh data before saving the consolidated sheet. This will ensure data remains intact upon reopening.
    • Non-VBA Approach: Use data validation and user-form inputs to link the data robustly, thereby minimizing direct dependencies.

Resources