VBA/Formula to reference a Closed Workbook, based on a Dynamic cell reference in current workbook

[SirGruffles]

Hello!

I'm trying to make a dynamic reference to a closed workbook in my report.

Currently, a straight-up hardcoded link looks like this:
='G:\Reports\Management Reporter\[India Trial Balance 2023 in US dollars v2.xlsx]ManagementReporterISbymonth'!J16

My goal is to have the bolded part be dynamically changeable.
I want the bolded part to be referenced to what is in cell B4 on my worksheet.
It doesn't work but I'm looking for something akin to:
='G:\Reports\Management Reporter\[B4]ManagementReporterISbymonth'!J16

Is this possible?

Thank you very much for any help! <3

 

[DanteAmor]

Try:

=INDIRECT("'G:\Reports\Management Reporter\[" & B4 & "]ManagementReporterISbymonth'!J16")

Consider that INDIRECT is a volatile function, if you close the book of the reference the #REF error will appear in the cell

 

[SirGruffles]

Try:

=INDIRECT("'G:\Reports\Management Reporter\[" & B4 & "]ManagementReporterISbymonth'!J16")

Consider that INDIRECT is a volatile function, if you close the book of the reference the #REF error will appear in the cell

My goal is to not require the user to have these spreadsheets open, and to be able to reference them while closed :/
But thanks for the reply!

 

[SirGruffles]

Update: I've got it kind of working....but I keep getting the "Update Values" pop-up, and it doesn't seem to identify my NamedRange as my reference.

I've named the range officially CurrentIndia in NameManager:

But everytime I edit the formula or re-confirm it, the Update Values window pops up everytime, and I have to manually select the file I have listed under CurrentIndia.
Is there a way for this reference to be pulled automatically so my end user doesn't need to select the file in the window?

Thanks!