Can Microsoft Excel Use Wildcards in External Workbook References?
Question details
Learn how to work with external Excel source files when the workbook name changes because it includes a date, timestamp, or other variable text.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants an external workbook reference to locate a source file whose name is not always the same.
- Observed behavior
- Excel does not support wildcard characters such as * or ? directly in external workbook or worksheet references.
Identify the folder that contains the changing source files and use a consistent naming pattern where possible. If you use INDIRECT, open the source workbook before recalculating the formula.
Import changing source files with Power Query
Use this recommended method when source workbook or CSV file names change regularly and the files are stored in one folder.
Power Query can connect to a folder rather than a single fixed file name. You can filter, sort, and combine files so the query loads the appropriate source data without placing wildcards in a worksheet reference.
Move the changing Excel or CSV source files into a dedicated folder, and keep the files in a compatible table or column structure.
In Excel, select Data > Get Data > From File > From Folder, choose the folder, and select Open.
In the folder preview, use Transform Data to filter by file extension or name pattern, then select Combine Files or load the selected file data.
After adding a new dated or timestamped file to the folder, select Data > Refresh All to update the imported results.
Build a fixed external reference with INDIRECT
Use this only when you can construct the exact file name in cells and the referenced workbook will remain open during calculation.
Manage spreadsheet imports with WPS Office
If you need a free, lightweight office suite for everyday spreadsheet work, WPS Office provides a familiar spreadsheet interface and supports seamless migration of your existing files. Open and edit Excel workbooks while keeping common formulas, tables, and worksheet layouts compatible.
Frequently Asked Questions
Can I use an asterisk in an Excel external workbook reference?
No. Excel external references require an exact workbook and worksheet name; wildcard characters such as * and ? are not supported in the reference path.
Why does an INDIRECT external reference return an error when the source file is closed?
INDIRECT generally requires the referenced external workbook to be open. Open the source workbook, then use Formulas > Calculate Now to refresh the formula.
How can I import the newest dated Excel file from a folder?
Use Data > Get Data > From File > From Folder in Excel. In Power Query, sort or filter the folder file list by name or modified date, then load the desired file's contents.
Can Power Query combine several CSV or Excel files with changing names?
Yes. Choose Data > Get Data > From File > From Folder, select the folder, and use Combine Files. The source files should use consistent columns and data structures.
