You need a value from another workbook — a price from the master price list, a total from last month’s report, or a setting from a shared configuration file. But you don’t want to keep that file open all day. Excel can reference closed workbooks with a special file-path formula syntax, and Power Query offers an even better approach for pulling structured data.
Method 1: Direct Cell Reference to a Closed Workbook
Excel supports referencing cells in closed files using this syntax:
='C:\Reports\[Q1_Sales.xlsx]Summary'!B5
C:\Reports\ = folder path | [Q1_Sales.xlsx] = filename in brackets | Summary = sheet name | B5 = cell reference
When the source file is open, it shows as a normal cross-workbook reference. When the file is closed, Excel adds the full path automatically.
Creating the reference while the file is open
The easiest way to build this formula: open both files, type = in your destination cell, click the cell in the source file, and press Enter. Excel writes the full path reference for you. When you close the source file, the formula automatically converts to the closed-file syntax.
Combining with VLOOKUP
You can use VLOOKUP against a closed workbook to look up values dynamically:
=VLOOKUP(A2, 'C:\Data\[PriceList.xlsx]Products'!A:C, 3, FALSE)
Looks up the value in A2 against the Products sheet in the PriceList.xlsx file, even when that file is closed.
Limitations of closed-file references
References to closed files only work with direct cell addresses and simple functions. Some functions (like OFFSET, INDIRECT) do not work with closed workbooks. The source file must be accessible at the path specified — if someone moves or renames it, all references break. Network paths work but are slower.
Method 2: Power Query (Better for Structured Data)
For pulling tables, ranges, or entire sheets from other files:
1Go to Data → Get Data → From File → From Workbook
2Browse to and select the source file
3Select the sheet or table you want to import
4Apply any transformations (filter rows, select columns, change types)
5Click Close & Load
The data loads into your workbook as a refreshable connection. Click Data → Refresh All anytime to pull the latest data from the source file.
Power Query vs formulas
Use direct formulas when you need a few specific cells from another file. Use Power Query when you need entire tables, multiple columns, or when you want to transform the data (filter, clean, reshape) during the pull.
Method 3: INDIRECT with a File Path (Workaround)
INDIRECT doesn’t natively work with closed files, but you can build a helper approach for dynamic file paths:
Build the reference as text
="'C:\Reports\["&B1&"]"&B2&"'!"&B3
Where B1=filename, B2=sheet name, B3=cell address. This builds the reference string, but you’d need a macro to evaluate it since INDIRECT can’t open closed files.
Handling Broken Links
When source files move or get renamed, your references break. To manage this:
1Go to Data → Edit Links (or File → Info → Edit Links to Files)
2You’ll see all external file references and their status
3Click Change Source to point to the file’s new location
4Click Update Values to refresh
When Formulas Aren’t Enough
Simple cross-file references work for a few values. But real multi-file workflows push beyond what formulas can manage:
- Pulling from dozens of files — referencing 12 monthly files or 50 branch reports manually isn’t practical
- Dynamic file names — filenames that change each month (“Sales_Jan2025.xlsx”, “Sales_Feb2025.xlsx”) can’t be handled with static formulas
- Folder scanning — automatically processing every file in a folder without knowing the filenames in advance
- Data consolidation + cleanup — pulling data from multiple files and combining, deduplicating, and formatting it
- Scheduled execution — running the pull automatically at a set time or on workbook open
A custom VBA file processor scans folders, opens files dynamically, extracts and consolidates data, handles naming variations, and runs on demand or on schedule.
“David is by far one of the best contractors. Not only did he fully understand what I wanted, he worked through the problems, redesigns, and additions that developed as we went.”
Excel Reporting Client · Upwork
Pulling Data from Multiple Files Manually?
Tell us about your file workflow and we’ll build a tool that consolidates everything automatically.
Start a Project