Fix Excel INDIRECT Errors with Spill Ranges and Closed Books

A user in an Excel StackOverflow community recently posted: “I am trying to build a master dashboard using LET to process data from several external workbooks. I’m using INDIRECT to construct the file path so I can toggle between departments, but as soon as I reference a spill range (like A1#) or close the source workbook, everything returns a #REF! error. How can I make my dynamic arrays work with closed external files?”

The Problem: Why INDIRECT Fails

The INDIRECT function is one of the oldest tools in Excel, and it hasn’t quite kept up with the “Big Grid” evolution (Dynamic Arrays). It has two fatal flaws for modern workflows:

1. The Closed Workbook Limitation: INDIRECT cannot pull data from a workbook that is not currently open in the same Excel instance.
2. Spill Range Incompatibility: INDIRECT often struggles to resolve the hash (#) spill operator when nested inside complex functions like LET or LAMBDA, because it evaluates the string before the array is fully calculated.

Comparing Solutions for Dynamic External Data

Method	Handles Closed Files?	Supports Spill Ranges?	Complexity	Recommendation
INDIRECT	No	Partially (buggy)	Low	Avoid for external files.
Direct Linking	Yes	Yes	Low	Best for static file paths.
Power Query	Yes	Yes	Medium	Best for dynamic/robust paths.
VBA/Macros	Yes	Yes	High	Use only if PQ is restricted.

---

The Recommended Fix: Power Query Parameterization

Since INDIRECT cannot look into closed files, the professional solution is to use Power Query to fetch the data. You can still make it “dynamic” by linking the file path to a cell in your workbook.

Tested on Excel for Microsoft 365 (Version 2402) as of 2024.

Step 1: Create a Path Parameter

1. Type the full path of your source workbook into an Excel cell (e.g., C:\Reports\Data2023.xlsx).
2. Highlight the cell and go to the Name Box (left of the formula bar) and name it FilePath.
3. With that cell selected, go to Data > From Table/Range.
4. In the Power Query editor, right-click the value in the cell, select Drill Down, and then rename this query to TargetFile. This converts the cell value into a dynamic variable.

Step 2: Connect to the External Data

1. Go to Home > New Source > File > Excel Workbook.
2. Pick any file for now to act as a template.
3. In the formula bar of the “Source” step, look for the hardcoded path in quotes. Replace it with your variable name: TargetFile.
   • Example: = Excel.Workbook(File.Contents(TargetFile), null, true)
4. Navigate to the sheet or table you need, and click Close & Load To….
5. Select Table and choose a destination on your worksheet.

Step 3: Reference the Result in LET

Now that your data is loading into a table (let’s call it ExternalData), you can use it in your complex LET formulas without any #REF! errors.

=LET(
    sourceData, ExternalData,
    filtered, FILTER(sourceData, sourceData[Amount] > 100),
    filtered
)

---

Why This Works

By using Power Query as a “middleman,” you bypass the limitations of the Excel calculation engine. Power Query connects to the file on the disk level, meaning the source workbook does not need to be open. Once the data is loaded into your workbook as a Table or a Spill Range (via the new IMAGE or DATA functions), LET treats it as local data, ensuring your dynamic arrays calculate instantly.

---

Common Troubleshooting Questions

What if I need to refresh the data automatically?

Unlike INDIRECT, which calculates instantly, Power Query requires a refresh. You can right-click the result table and select Refresh, or go to Data > Queries & Connections > Properties and set the workbook to “Refresh data when opening the file.”

Can I use this for multiple files at once?

Yes. If your LET function needs to aggregate data from a whole folder, use Data > Get Data > From File > From Folder. Power Query will combine all files in that folder into one table, which you can then manipulate with dynamic array functions.

Will this work in Excel for the Web?

Power Query support in Excel for the Web is improving, but it cannot currently refresh local file paths (C:\Users...). If your files are stored on SharePoint or OneDrive, you can use the Web URL in Power Query, and it will refresh perfectly in the browser version.