Referencing a workbook with a variable file name

[Pawlz]

Hi All,

I hope you are well!

Does anyone know of a way of referencing a workbook with a variable file name in a formula? The formula is quite simple, just a SUM formula that references a cell on a sheet in a different workbook. The formula is :

=SUM('J:\Production\Pawlz\Daily\[SHIFT REPORT WC 01.01.24.xlsx]Monday '!$D$78:$D$83)

Basically, just adding the total of cells D78 through to D83 and there are 56 formulas of this type on the worksheet. A new SHIFT REPORT file is created each week and stored in the same location (so we end up with 52 files, 1 for each week, at the end of the year). Currently, I overwrite SHIFT REPORT WC 01.01.2024.xlsx each week with the current week's workbook. I am looking for a way to automatically change the formula to the current week commencing file.

I have already got a series of formulas that generate the file name from the current date - [SHIFT REPORT WC 01.01.2024.xlsx]. I was hoping to somehow integrate this into a formula so it changes automatically based on the current date.

Or is there an easier solution?

Cheers

Pawlz

 

[RoryA]

You can use INDIRECT but it will only work if the source file is open. I'd just use the edit links dialog to change the source workbook.