Pivot Table Issues

How to Fix GETPIVOTDATA #REF! Errors When an Excel Source File is Closed

Elise Williams Oct 1, 2026 868 views

Question details

The user needs a solution to fix GETPIVOTDATA functions returning a #REF! error when the source PivotTable or workbook is closed, as well as a way to resolve unexpected OneDrive sign-in prompts for d.docs.live.net paths.

How to Fix GETPIVOTDATA #REF! Errors When an Excel PivotTable is Closed

Product: Microsoft Excel
Device & OS: not provided
Scenario: Pulling data from an external PivotTable using GETPIVOTDATA for a dashboard or report, where the source file needs to be closed.
Observed behavior: The GETPIVOTDATA formula returns a #REF! error immediately after the source file is closed. Additionally, OneDrive repeatedly prompts for sign-in when accessing files via d.docs.live.net links.

Before you start

Ensure both the destination workbook and the source workbook containing the PivotTable are accessible, saved in a trusted location, and that you have the necessary file permissions.

Solution 1Recommended

Calculate GETPIVOTDATA Internally and Link to the Result

Bypass the closed-file limitation by performing the GETPIVOTDATA calculation inside the source workbook and linking to that specific cell from your destination file.

The GETPIVOTDATA function requires the source PivotTable's cache to be actively loaded in memory. This means it inherently fails when the source file is closed. Linking to a standard cell value circumvents this requirement.

Open Source Workbook

Open the Excel file that contains your original PivotTable.

Extract Data Internally

In a blank cell outside the PivotTable (but within the same workbook), write the GETPIVOTDATA formula to extract the required value.

Link to Destination Workbook

Open your destination dashboard file. Type '=' in the desired cell, switch to the source workbook, click the cell where you just calculated the GETPIVOTDATA result, and press Enter.

Calculate GETPIVOTDATA Internally and Link to the Result

Dashboard Updates: By directly linking to a calculated cell, your dashboard will continue to display the last known value even when the source file is closed.

Solution 2

Use INDEX and MATCH for Closed Workbooks

Unlike GETPIVOTDATA, standard reference functions like INDEX and MATCH can retrieve data from closed workbooks without returning a #REF! error.

Solution 3

Switch from d.docs.live.net to a Locally Synced Path

Fix continuous sign-in requests by using your local OneDrive sync folder instead of direct web URLs.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

If you are tired of dealing with complex external link errors, #REF! issues, and cloud sync interruptions, consider WPS Office. It provides a lightweight, highly compatible, and seamless alternative to Microsoft Office for all your spreadsheet and data analysis needs.

1. Download WPS Office: Visit the official WPS Office website and download the free installer for your operating system.
2. Install the Software: Run the setup file and follow the straightforward on-screen instructions to complete the installation.
3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Excel files to easily manage PivotTables and external links.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Advanced PivotTable and data analysis features without heavy resource usage.Free, lightweight, and features an intuitive interface familiar to Excel users.Reliable built-in file management that minimizes link disruption.

microsoft office alternative - wps office

Frequently Asked Questions

Why does GETPIVOTDATA return #REF! when the source file is closed?

The GETPIVOTDATA function requires the source PivotTable's cache to be actively loaded in the application's memory. When the external workbook is closed, the spreadsheet program cannot query the PivotTable structure, which results in a #REF! error.

How do I stop OneDrive from asking for sign-in via d.docs.live.net?

This usually happens when an Excel workbook references a cloud URL instead of a local file path. You can stop the prompts by going to Data > Edit Links, and changing your external links to point to the locally synced OneDrive folder on your computer's hard drive.

Will VLOOKUP or INDEX/MATCH work on a closed workbook instead of GETPIVOTDATA?

Yes. Standard lookup functions like VLOOKUP, HLOOKUP, INDEX, and MATCH can successfully pull data from a closed external workbook without returning an error, making them a preferred alternative for closed-file data retrieval.

Can I refresh a PivotTable if its source data file is closed?

Yes. If your PivotTable is connected to an external data source or another closed workbook, you can refresh it as long as the file path remains valid and you grant permission to update external links when prompted.

Excel & Data Problems