Creating a dynamic file reference

[austinghamilton]

I need help creating a dynamic file reference in Excel (Please let me know the correct terminology as well). Currently, it is
"='[SDR2021_AFG_NN.xls]Forecast NN 29 -- T5B5 -- AFG'!C2"

I want to do something like this:
"='[SDR2021_$P$94_NN.xls]Forecast NN 29 -- T5B5 -- $P$94'!C2"

In cell P94, in the same sheet as the formula, I have entered "AFG".

Please advise.

 

[Fluff]

Hi & welcome to MrExcel.
Will the other workbook be open?

 

[austinghamilton]

Hi & welcome to MrExcel.
Will the other workbook be open?

Thank you for your quick reply! I was planning on having the other Excel open to avoid issues, but if you know a workaround to allow the link and the other Excel to be closed that would be even better. Please advise.

 

[Fluff]

Ok, try it like

=INDIRECT("'[SDR2021_"&P94&"_NN.xls]Forecast NN 29 -- T5B5 -- "&P94&"'!C2")

although this will not work on closed workbooks.

 

[austinghamilton]

Ok, try it like

=INDIRECT("'[SDR2021_"&P94&"_NN.xls]Forecast NN 29 -- T5B5 -- "&P94&"'!C2")

although this will not work on closed workbooks.

Thank you for the code! It worked perfectly!

 

[Fluff]

You're welcome & thanks for the feedback.