Convert concated formula into real formula

[Joe006]

Picture 1:

Picture 2:

Hi,

How can i convert concated formula in (Picture 1) into real formula so that it can call the data and change according to the choosen month and year (Picture 2). I tried INDIRECT with the concated but got reference error.
Any help is much appreciated. Thank you

 

[StephenCrump]

Welcome to the Forum!

Try: =INDIRECT("'X:\OT Management\"&D2&C2&"\[9023 Smold.xlsx]Summary'!C$12")

 

[Joe006]

Welcome to the Forum!

Try: =INDIRECT("'C:\OT Management\"&D2&C2&"\[9023 Smold.xlsx]Summary'!C$12")

Hi, i tried and still got the reference error, data not showing as above which is 5583

 

[Joe006]

Welcome to the Forum!

Try: =INDIRECT("'X:\OT Management\"&D2&C2&"\[9023 Smold.xlsx]Summary'!C$12")

update: When I open the excel files that it refers to, it works, it get the data. How to make it get the data while the file is closed?

 

[Alex Blakenburg]

You can't use INDIRECT to access a "closed" workbook.
You will need to consider using VBA to build the formula with the current workbook name or use Power Query to import the data.

 

[Joe006]

You can't use INDIRECT to access a "closed" workbook.
You will need to consider using VBA to build the formula with the current workbook name or use Power Query to import the da

You can't use INDIRECT to access a "closed" workbook.
You will need to consider using VBA to build the formula with the current workbook name or use Power Query to import the data.

Hi, thanks for the answer, will consider using VBA