Power Query Problems

Create Dynamic Excel References for Weekly Report Workbooks Without VBA

John WilsonJohn Wilson Nov 10, 2026 869 views

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.

How to Create Dynamic Excel References for Weekly Report Workbooks
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.
Before you start

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'.

Solution 1Recommended

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.

1
Create a Dynamic Date Cell

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").

2
Name the Parameter Cell

Select the cell containing your formula, click the Name Box (next to the formula bar), and name it 'ReportDate'. Press Enter.

3
Import the Data to Power Query

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.

4
Modify the Source Step in Advanced 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.

5
Close and Load

Click 'Close & Load' to return the dynamic data to Excel. Your query will now update based on the formula's date.

Use Power Query to Create Dynamic File References
Automatic Updates: Once configured, you only need to click 'Refresh All' on the Data tab each week. Power Query will automatically look for the file name matching your dynamic date cell.

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. 1. Create Your Master Workbook: Open WPS Spreadsheet and start a new workbook for your consolidated weekly report.
  2. 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. 3. Link Your Data: Utilize the built-in Data import tools or reference functions to connect to your weekly source files.
  4. 4. Save and Share: Save your master report in the standard .xlsx format to ensure seamless collaboration with any Microsoft Excel user.
Fully compatible with Microsoft Excel (.xlsx) formats and standard formulasSupports dynamic date and text functions like TEXT, TODAY, and INDIRECTLightweight application that processes complex datasets smoothlyFree to download with an intuitive user interface for streamlined reporting
microsoft office alternative - wps office

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.