Power Query Problems

How to Use Power Query Instead of INDIRECT for Variable Excel Paths

Camila Milosovich Oct 22, 2026 868 views

Question details

The user wants to retrieve data from external Excel workbooks where folder or file names change dynamically (e.g., weekly), without encountering reference errors when the source files are closed.

How to Use Power Query Instead of INDIRECT for Variable Excel Paths

Product: Excel
Device & OS: not provided
Scenario: Importing or consolidating data from multiple external workbooks that have changing paths or file names.
Observed behavior: Using the INDIRECT function causes a #REF! error when the referenced external workbook is closed.

Before you start

Ensure that the external workbooks you want to import are saved in a dedicated folder and that they share a consistent table structure or column headers.

Solution 1Recommended

Use Power Query's 'From Folder' Feature

Use Power Query to connect directly to the folder containing your files, avoiding the volatile INDIRECT function and closed-workbook errors.

The INDIRECT function in Excel is volatile and explicitly requires the source workbook to be open. When the target file is closed, INDIRECT evaluates to a #REF! error.

Power Query solves this by establishing a direct connection to the external file or folder. It reads the data independently of Excel's calculation engine, allowing you to pull data from closed files and easily handle dynamically changing file names by querying the entire folder.

Open Get Data Menu

In Excel, navigate to the Data tab on the ribbon and click on Get Data.

Select Folder Source

Choose From File, and then select From Folder from the dropdown menu.

Browse and Connect

Browse to the specific folder path where your weekly reports or variable Excel files are stored, then click Open.

Combine Files

In the preview dialog box that appears, click the Combine dropdown at the bottom and select Combine & Transform Data.

Load Data

Apply any necessary filters in the Power Query Editor, then click Close & Load to export the consolidated data into your current worksheet or Data Model.

Automatic Updates: Once set up, you can simply drop new weekly files into the target folder and click 'Refresh All' on the Data tab to update your spreadsheet instantly.

Free Microsoft Office alternative

Try WPS Office for Your Spreadsheet Needs

If you are looking for a highly compatible, cost-effective solution for handling complex spreadsheets and data processing, WPS Office is a fantastic free alternative to Microsoft Office.

1. Download the Installer: Visit the official WPS Office website and download the free installation package.
2. Install WPS Office: Run the installer and follow the quick on-screen instructions to complete the setup.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files with full formatting and formula compatibility.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsLightweight installation with incredibly fast launch speedsFamiliar user interface ensuring a seamless transition and zero learning curveBuilt-in robust data processing and visualization tools

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error with external links?

The INDIRECT function requires the referenced external workbook to be open in the background. If the referenced workbook is closed, Excel cannot dynamically evaluate the path, which instantly results in a #REF! reference error.

Can I dynamically change the folder path in Power Query?

Yes. You can create a parameter in Power Query that links to a specific cell value in your Excel workbook. This allows you to dynamically change the target folder path directly from the worksheet without having to open the Power Query Editor.

What is the most reliable way to handle weekly report files with changing names?

Saving all related weekly reports into a single designated folder and using Power Query's 'From Folder' feature is the most reliable method. Power Query will automatically detect and consolidate all files within that folder regardless of weekly name variations.

Excel & Data Problems