Create Dynamic Excel References for Weekly Report Workbooks Without VBA
Question details
The user wants to build dynamic references to weekly Excel report files with changing file names based on dates and week numbers, specifically without relying on VBA.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating weekly reporting by importing data from files that have dynamic names based on the current date or week number.
- Observed behavior
- Standard formulas like INDIRECT generally fail because they cannot retrieve data from closed workbooks, leaving users looking for an automated alternative like Power Query.
Ensure that all your weekly report workbooks are saved in a consistent folder location and follow a predictable naming structure, such as 'Report_DDMMYY.xlsx'.
Use Power Query to Create Dynamic File References
Power Query is the most reliable non-VBA method to pull data from closed workbooks with dynamic file names.
Unlike the INDIRECT function, which requires the target workbook to remain open, Power Query can seamlessly retrieve data from closed workbooks. You can set up a parameter based on a dynamic Excel formula to feed the correct file path into your query automatically.
In your main workbook, pick a cell (e.g., G3) and enter a formula to generate the date string that matches your file naming convention. For example: =TEXT(TODAY()-4,"DDMMYY").
Select the cell containing your formula, click the Name Box (next to the formula bar), and name it 'ReportDate'. Press Enter.
Go to the Data tab, select 'Get Data' > 'From File' > 'From Workbook', and choose your current week's report to load it into the Power Query Editor.
In Power Query, click 'Advanced Editor' on the Home tab. Replace the hardcoded file path in the 'Source' step with a variable that points to your 'ReportDate' named range parameter.
Click 'Close & Load' to return the dynamic data to Excel. Your query will now update based on the formula's date.

Use the INDIRECT Function (Alternative for Open Workbooks)
A quick formula-based approach suitable only if the source workbooks will be open during data retrieval.
Handle Dynamic Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides robust data handling capabilities, fully supporting advanced text and date functions required for dynamic weekly reporting. Enjoy seamless data processing and full compatibility with Microsoft Excel files.
- 1. Create Your Master Workbook: Open WPS Spreadsheet and start a new workbook for your consolidated weekly report.
- 2. Set Up Dynamic Formulas: Use formulas like =TEXT(TODAY()-4,"DDMMYY") to automatically generate the file name for the current week's report.
- 3. Link Your Data: Utilize the built-in Data import tools or reference functions to connect to your weekly source files.
- 4. Save and Share: Save your master report in the standard .xlsx format to ensure seamless collaboration with any Microsoft Excel user.

Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error when linking to weekly reports?
The INDIRECT function requires the referenced source workbook to be open in the background. If the weekly report file is closed, Excel cannot resolve the dynamic text string into a valid reference, resulting in a #REF! error. Using Power Query bypasses this limitation.
How do I format an Excel date to match my file name using formulas?
You can use the TEXT function combined with TODAY() or another date reference. For example, if your file is named 'Report_051223.xlsx', you can generate this dynamically by using the formula ="Report_"&TEXT(TODAY(),"DDMMYY")&".xlsx".
Can Power Query pull data from a specific worksheet within a dynamic workbook?
Yes. Once you pass the dynamic file path into Power Query to locate the workbook, the Navigation step in the query will allow you to specify the exact sheet name (e.g., 'Sheet1') you want to extract data from.
Is it possible to consolidate multiple weekly reports at once without VBA?
Yes, you can use Power Query's 'Get Data from Folder' feature. This allows you to point to a specific directory containing all your weekly reports and automatically combine their contents into a single master table without writing any VBA code.




