Function Problems

How to Retrieve Data from Dated Excel Workbooks in SharePoint

WPS Editor Oct 1, 2026 869 views

Question details

The user needs to dynamically extract data from a specific cell (C29) across multiple daily laboratory workbooks stored in a SharePoint folder.

How to Retrieve Data from Dated Excel Workbooks in SharePoint

Product: Excel
Device & OS: not provided
Scenario: Pulling daily data logs from sequential, date-named workbook files stored on a SharePoint site for consolidated reporting.
Observed behavior: The INDIRECT formula used for data retrieval fails or returns errors because it cannot pull data from closed external workbooks.

Before you start

Verify that you have the correct URL to your SharePoint site and ensure you have at least read permissions for the folder containing the daily laboratory workbooks.

Solution 1Recommended

Use Power Query to Extract Data from SharePoint

Since the INDIRECT function does not work with closed workbooks, Power Query is the recommended method to connect to a SharePoint folder and extract specific rows and columns from multiple files.

The INDIRECT formula strictly requires referenced external workbooks to be actively open in the background to evaluate the data. For pulling daily data from a repository of closed workbooks, Power Query provides a robust, automatable solution.

By connecting directly to the SharePoint directory, you can retrieve the necessary files, expand their contents, and drill down to specific cells using zero-based indexing.

Open Get Data

In a new workbook, go to the 'Data' tab on the ribbon, click 'Get Data', select 'From File', and then click 'From SharePoint Folder'.

Connect to SharePoint

Paste your root SharePoint site URL into the dialog box and click 'OK'. Authenticate your Microsoft or organizational account if prompted.

Filter and Transform

In the preview window, locate your dated workbooks. Click 'Transform Data' to open the Power Query Editor, then click the 'Combine Files' icon on the Content column to expand the data.

Extract Specific Cell Data

To get row 29 and column 3 (C29) from each file, use a zero-based query index in the formula bar. The structure will resemble `= #"Expanded Table Column1"{28}[Column3]`, referencing the 28th index (row 29).

Load the Data

Once your data is successfully extracted and formatted, click 'Close & Load' in the top-left corner to import the consolidated SharePoint data into your current workbook.

Use Power Query to Extract Data from SharePoint

Understanding Zero-Based Indexing: Power Query starts counting rows at 0 instead of 1. Therefore, when attempting to retrieve data from row 29 of your laboratory workbook, you must reference index number 28 in your custom query.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

Struggling with complex Excel formulas, closed workbook limitations, and SharePoint connections can disrupt daily workflows. WPS Office offers a highly compatible, easy-to-use alternative that handles large data logs efficiently without heavy system resource demands.

1. Download the Installer: Visit the official WPS Office website and download the free desktop application for your operating system.
2. Install WPS Office: Run the downloaded file and follow the standard installation prompts to set up the software suite.
3. Manage Data Logs: Open WPS Spreadsheet, load your daily laboratory workbooks, and enjoy a streamlined data management experience.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Lightweight architecture ensures fast loading of large daily data logs.Intuitive and familiar interface completely eliminates the steep learning curve.Completely free to use for daily spreadsheet tracking, calculations, and reporting.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error when checking SharePoint files?

The INDIRECT function evaluates dynamic text strings into cell references, but it requires the target external workbook to be open in the background. If the dated SharePoint workbooks are closed, Excel cannot resolve the reference, resulting in a #REF! error.

Can I link closed SharePoint workbooks without using Power Query?

If you sync your SharePoint document library to your local computer using OneDrive, you can treat the files as local assets. You can create direct formula links to these files (e.g., ='C:\Users\Path\[File.xlsx]Sheet1'!C29), which will work when closed, unlike INDIRECT.

What is the easiest way to reference cell C29 in a Power Query step?

Once you have expanded the table data in Power Query, you can append an index reference to your applied step. Because Power Query uses zero-based indexing, cell C29 translates to row index {28} and the exact column header name in brackets, such as [Column3].

Excel & Data Problems