Reading worksheet path/name from a cell and using it as lookup_array in a X/VLOOKUP function

[RatzaTM]

Hello folks,
So I've been bugging over this for a week or so and can't seem to figure it out, I'm hoping someone can shed some light.
There's an Excel_1 I've made which reads information from Excel_2,3,4,etc. The formula looks like this:

XLOOKUP(C5, 'C:\March\[01.03.2023 name_of_file.XLSX]Current'!D:D, 'C:\March\[01.03.2023 name_of_file.XLSX]Current'!C:C)

The problem that I have is the source XLSX changes name each week or month and I have to manually adjust each formula to take into account the new filename. I was thinking of having either a specific cell or a named range or a variable of some sort in Excel_1, that I could update and all the formulas would use it as the new filename, say C:\April\[01.04.2023 name_of_file.XLSX]Current', without having to manually replace the text with the new path each time for every formula.

I tried named ranges, tried INDIRECT with the 2 books open, there's always something preventing the lookup from reading the target worksheet. Does anyone have a better idea on how this can be achieved? I want the formulas to be a static reference to something that I can change anytime I want, in a single location.

Thanks a bunch, in advance